Programming Extended SQL-DMO Objects

  • Article

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

SQL Distributed Management Objects (SQL-DMO) in Microsoft SQL Server 2000 featured a number of new objects that were not compatible with earlier releases. Most of those objects are named in the form of ObjectName2, and extend the functionality of similarly named objects supported by SQL Server version 7.0. For example, the UserDefinedDataType2 object extends the functionality of the UserDefinedDataType object by exposing the Collation property. Objects such as UserDefinedDataType2 inherit the methods and properties of their base objects. Therefore, an application can always use the UserDefinedDataType2 object to call the methods and properties of the UserDefinedDataType object.

It is unnecessary to modify existing SQL Server 7.0 applications, because they do not reference the new objects, methods, and properties exposed in SQL Server 2000 and SQL Server 2005.

Using C++ with the Extended SQL-DMO Objects

C++ applications that use the new SQL-DMO objects do not need to take any extra programmatic steps if the application will only be used with instances of SQL Server 2000 and SQL Server 2005. However, C++ applications that use the new SQL-DMO objects and also are used with SQL Server 7.0 will encounter an error if trying to use a new object. Therefore, the application must call the IUnknown::QueryInterface method to use an ObjectName2 object with the related object from which it inherits, and to handle errors gracefully.

These examples demonstrate how to use ObjectName2 objects using the Collation property of the UserDefinedDataType2 object. The first example demonstrates usage in an application that runs with SQL Server 2000 or SQL Server 2005 only. The second example demonstrates usage in an application that might also run with SQL Server 7.0.

Examples

Referencing the extended SQL-DMO objects with SQL Server 2000 or SQL Server 2005

//Define variable.
LPSQLDMOUSERDEFINEDDATATYPE2 oUDDT2 = NULL;

// Do CoCreate Instance for UserDefinedDataType.
CoCreateInstance(CLSID_SQLDMOUserDefinedDataType, NULL, CLSCTX_INPROC_SERVER, IID_ISQLDMOUserDefinedDataType2, (LPVOID*) &oUDDT2))

oUDDT2->SetCollation(L"German_Phonebook_CI_AI_KI_WI");

// Now add the UserDefinedDataType object to the UserDefinedDataTypes // collection.

Referencing the extended SQL-DMO objects with SQL Server 7.0 or later

// Define variables.
LPSQLDMOUSERDEFINEDDATATYPE oUDDT = NULL;
LPSQLDMOUSERDEFINEDDATATYPE2 oUDDT2 = NULL;
HRESULT hr;

// Do CoCreate Instance for UserDefinedDataType.
CoCreateInstance(CLSID_SQLDMOUserDefinedDataType2, NULL, CLSCTX_INPROC_SERVER, IID_ISQLDMOUserDefinedDataType, (LPVOID*) &oUDDT))

// QueryInterface UserDefinedDataType2.
// Gracefully handle error situations arising from use with version // 7.0.
hr=oUDDT->QueryInterface(IID_ISQLDMOUserDefinedDatatype2,&oUDDT2);
if (SUCCEEDED(hr))
oUDDT2->SetCollation(L"German_Phonebook_CI_AI_KI_WI");
else
// oUDDT2 is not supported. Perform error handling routine.

// Now add the UserDefinedDataType object to the UserDefinedDataTypes // collection.

Using Visual Basic with the Extended SQL-DMO Objects

Visual Basic applications that use the new SQL-DMO objects do not need to take any extra programmatic steps if the application will only be used with Microsoft SQL Server 2000 or Microsoft SQL Server 2005. No extra steps are required for Visual Basic applications that use late binding. However, Visual Basic applications that use early binding must be precise in setting an ObjectName2 object variable. For example, in this code sample, the StoredProcedures.Item method returns a StoredProcedure object, not a StoredProcedure2 object:

Dim oSQLSvr2 as New SQLServer2
oSQLSvr2.Connect "Myserver","sa",""
MsgBox oSQLSrv2.Databases("northwind").StoredProcedures(1).Name

However, using this approach, the StoredProcedures.Item method calls the IUnknown::QueryInterface method for the StoredProcedure2 object:

Dim oStoredProc2 as SQLDMO.StoredProcedure2
Set oStoredProc2 = oSQLSrv2.Databases("northwind").StoredProcedures(1)
oStoredProc2.IsDeleted