Programming Extended SQL-DMO Objects

SQL-DMO in Microsoft® SQL Server™ 2000 features a number of new objects compatible only with this release. Most of these new 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 version 7.0 applications, because they do not reference the new objects, methods, and properties exposed in SQL Server 2000.

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 SQL Server 2000. However, C++ applications that use the new SQL-DMO objects and also are used with SQL Server version 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 only. The second example demonstrates usage in an application that might also run with SQL Server version 7.0.

Examples

A. Referencing the extended SQL-DMO objects with SQL Server 2000

//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.

B. Referencing the extended SQL-DMO objects with SQL Server 2000 or SQL Server version 7.0

//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 SQL Server 2000. 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