The core of SQL-DMO is the SQLServer object. For each SQL Server you want to connect to, you create an empty SQLServer object, then you connect the SQLServer object to a running SQL Server.
The methods described here are used to create all types of creatable SQL-DMO objects, including a SQLServer object. When a new SQL-DMO object is created, the object is empty, and its properties are initialized to default values.
There are three methods for creating SQL-DMO objects in Visual Basic. All three methods involve using the Dim statement to declare an object variable of a certain type.
The SQL-DMO application name is SQLOLE. It is recommended that you use the application name SQLOLE when creating and declaring SQL-DMO objects. This avoids ambiguity if another OLE components includes objects with common names, such as Table or Database.
Specific SQL-DMO object type
The preferred method (if your version of Visual Basic supports it) is to use a Dim statement to declare an object variable of a specific SQL-DMO object type. This method requires that the SQL-DMO type library be included in your Visual Basic project. To actually create a new instance of a SQL-DMO object, you can use the New keyword in your Dim statement (if your version of Visual Basic supports it), or you can use CreateObject function.
For example, to create a new SQLServer object using the New keyword:
Dim oSQLServer As New SQLOLE.SQLServer
For example, to declare and create a new SQLServer object using the CreateObject function:
Dim oSQLServer As SQLOLE.SQLServer
Set oSQLServer = CreateObject ("SQLOLE.SQLServer")
Some versions of Visual Basic use virtual function table binding (vtable-binding), or early-binding. The SQL-DMO custom interfaces are used directly (not through the OLE IDispatch interface), resulting in the best performance.
Some versions of Visual Basic use a form of late-binding called dispatch ID binding (dispID-binding) using OLE IDispatch. The IDispatch::Invoke function is used with the dispIDs included in the SQL-DMO type library. This method will give your application slower performance than using the New keyword, but faster performance than using the Object type.
Generic Object type
The other method (supported by all versions of Visual Basic) is to use a Dim statement to declare a new variable of the generic Object type, then use the CreateObject function to create a new instance of a SQL-DMO object and assign it to the object variable. This method does not require the SQL-DMO type library.
For example, to declare and create a new SQLServer object using the generic Object type:
Dim oSQLServer As Object
Set oSQLServer = CreateObject ("SQLOLE.SQLServer")
This method is called late-binding using OLE IDispatch. The IDispatch::GetIDsOfNames function is used to get the dispIDs, which are passed to the IDispatch::Invoke function. This method will give your application slower performance than declaring an object variable of a specific SQL-DMO object type.
There is only one method to create a SQL-DMO object in C++. Declare an object variable of the appropriate SQL-DMO "pointer to an object" type. For an object named Object, the SQL-DMO object type is LPSQLOLEOBJECT. Then use the OLE CoCreateInstance function to create a new instance of a SQL-DMO object. You pass the following parameters to CoCreateInstance:
For example, to create a new SQLServer object:
LPSQLOLESERVER pSQLServer = NULL;
hr = CoCreateInstance (CLSID_SQLOLEServer,
NULL,
CLSCTX_INPROC_SERVER,
IID_ISQLOLEServer,
(LPVOID*)&pSQLServer);
Note A multithreaded program must serialize all access to SQL-DMO to avoid reentrancy.