Extend SQL Server with COM Automation

Automate COM objects from within seven SQL Server stored procedures

SQL Server's T-SQL programming language provides powerful facilities for data storage and retrieval, but T-SQL is weak when it tries to interact with systems outside the database. You can overcome this limitation, however, by using SQL Server's built-in COM automation environment, which lets you automate COM objects from within stored procedures. SQL Server 7.0 and SQL Server 6.5 provide seven extended stored procedures that let you extend SQL Server with COM objects you develop yourself or with those available in existing applications such as Microsoft Office. SQL Server also provides an error-handling mechanism that lets you pass errors up your application's hierarchy or place them in the SQL Agent log. With COM automation, you can integrate SQL Server with Microsoft Exchange Server, Microsoft Index Server, legacy systems, and anything else you can control through COM automation.

SQL Server 6.5 introduced the object automation environment, initially called OLE automation. But times changed and so did the name for object automation. However, because the automation environment didn't change from SQL Server 6.5 to SQL Server 7.0, Microsoft's documentation still calls this functionality OLE automation instead of COM automation—something to keep in mind when you search SQL Server Books Online (BOL) for more information. Let's look at how you use SQL Server's COM automation stored procedures and how COM automation can help you solve real-world programming problems.

Ins and Outs of COM Automation

Table 1 lists SQL Server's seven extended stored procedures for COM automation, which you find in SQL Server's master database. When automating an object, you first create an instance by calling sp_OACreate. You then make a series of calls to sp_OAGetProperty, sp_OASetProperty, and sp_OAMethod to accomplish your desired task. When you're finished with the object, you call sp_OADestroy. Before looking at each of the stored procedures in detail, note two important caveats. First, you must supply all parameters by position because the automation function doesn't support named parameters. If you aren't using a particular parameter, you need to pass a value of NULL as a placeholder. Second, each procedure returns an HRESULT value of type int, which is 0 if the call succeeds. Later in the article, I discuss how to handle nonzero return values.

COM automation begins with a call to stored procedure sp_OACreate, which uses the following syntax:

sp_OACreate progid | clsid, objecttoken OUT.PUT,   [context]

The first parameter uses either program ID (ProgID—a string in the form application_name.class_name, like Excel.Application) or class ID (CLSID—a 128-bit globally unique ID—GUID—value in the form nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn) to identify the COM object you want to create an instance of. I recommend you use ProgID values whenever possible because they're easy to type and remember. However, you'll find a few circumstances in which an object you want to automate doesn't have a ProgID, in which case you have to use the CLSID. The next parameter, objecttoken, should be a local variable declared as type int. An object token is a handle or pointer to the object that SQL Server creates. You use the returned objecttoken value to identify the object in all further calls to automation procedures. The last parameter, context, is optional and lets you force a certain kind of automation mechanism on the object created. A value of 1, for example, requires the object to be in an ActiveX DLL for in-process automation. A value of 4 requires the object to be in an ActiveX EXE server for out-of-process automation. And a value of 5, the default, allows either method of automation. I recommend you stick with the default, in which case you don't need to supply a value for the context parameter. The following statement calls sp_OACreate to create an instance of a Microsoft Excel application:

Declare @Object intDeclare @RetVal intExec @RetVal=sp_OACreate 'Excel.Application',   @Object OUTPUT

After creating an object, you might need to read some of its property values, which you can do by calling stored procedure sp_OAGetProperty with the following syntax:

sp_OAGetProperty objecttoken, propertyname[, propertyvalue OUTPUT] [, index]

The first parameter is the objecttoken value that sp_OACreate returns. Propertyname is the name of the object property whose value you want to retrieve. You have several options for obtaining the property value. If the property value is a single value, you can place it in a local variable or accept it as a single-row, single-column result set. If the property value is an array of one or two dimensions, you must accept it as a result set. If the property value is an array with more than two dimensions, sp_OAGetProperty can't return the value and an error will occur. To return a result set, simply don't specify the propertyvalue parameter (or if you need a value in propertyvalue to use the index parameter, place a NULL in propertyvalue's place). Otherwise, supply a variable of the appropriate type for the propertyvalue parameter, and be sure to mark the parameter as OUTPUT. You use the index parameter when the property you're accessing is a collection and you need to specify a particular member of the collection. If an object property returns another object, you should place the object in a variable of type int. The property value that sp_OAGetProperty returns will be an objecttoken, not unlike the ones you receive from calling sp_OACreate. You can then use this objecttoken to automate any object the stored procedure returns. The following statement calls sp_OAGetProperty to place the value of a property called DefaultFilePath into the local variable @DFP:

Exec sp_OAGetProperty @Object, 'DefaultFilePath',   @DFP OUTPUT

You change an object's property values by using stored procedure sp_OASetProperty with the following syntax:

sp_OASetProperty objecttoken, propertyname,   newvalue [, index]

The first parameter is the objecttoken that sp_OACreate returns. Propertyname is the name of the object property whose value you want to change. Newvalue, the new value you want to assign to the property, can be either a local variable or a literal value. If the property value you're setting belongs to an object that is part of a collection, you can use the optional index parameter to specify a particular member of the collection. The following statement calls sp_OASetProperty to set the property called FixedDecimalPlaces to the integer 6:

Exec sp_OASetProperty @Object, 'FixedDecimalPlaces', 6

To execute an object's method, you call stored procedure sp_OAMethod with the following syntax:

sp_OAMethod objecttoken, methodname [, returnvalue OUTPUT] [, [@parametername =] parameter [OUTPUT]...]

Sp_OAMethod is the most flexible and, therefore, most complex of all the automation procedures. You can even use it to call a property as a method and accept a return value, although you can use sp_OAGetProperty just as easily to perform these functions. The stored procedure's first parameter is the objecttoken that sp_OACreate returns. Methodname is the name of the object method you want to execute. If the method has a return value, the next parameter, returnvalue, should be a local variable of the appropriate type to contain the value that the method returns. If the return value is an array of one or two dimensions, use NULL as a placeholder; the procedure will return the value as a result set. The procedure, however, can't return as a result set an array with more than two dimensions; in this case, SQL Server will raise an error. If the method doesn't have a return type, use NULL as a placeholder.

If the method has parameters, you supply those next in your call to sp_OAMethod. If the method lets you supply parameters only by position (or if you're supplying all the method's parameters), you can just place the parameters in order, separated by commas. You can supply the values for the parameters either as local variables or literal values. If you need to use named parameters, SQL Server provides an unusual mechanism for doing so. You just list your parameters as parametername = parametervalue, with an at sign (@) preceding the parameter name. Don't be confused by the appearance that your parameter name is a local variable because of the @ prefix. When you call stored procedure sp_OAMethod, SQL Server parses out the at signs. So, you can have a local variable such as @HostName, even if you're calling a method that has a HostName parameter.

Following are two examples of calling sp_OAMethod. The first example calls a method named CentimetersToPoints. The procedure accepts only one parameter, which you provide in the @CMVal variable, and it returns a value in the @RetVal variable. The second example calls a method named MailLogon, which can accept three optional parameters. This example passes in the two parameters by name, setting Name to the string literal 'MyUserName' and setting Password to the string literal 'pwd'.

Exec sp_OAMethod @Object, 'CentimetersToPoints',   @RetVal OUTPUT, @CMValExec sp_OAMethod @Object, 'MailLogon', NULL,   @Name='MyUserName', @Password='pwd'

When you're finished using an object, you must release the reference to it by calling stored procedure sp_OADestroy with the following syntax:

sp_OADestroy objecttoken

Calling sp_OADestroy releases the reference to the object that objecttoken specifies and, in turn, releases any memory or other resources that the object is using. The following statement calls sp_OADestroy:

Exec sp_OADestroy @Object

Note that T-SQL's data types don't map one-for-one to data types in other programming languages, which could cause problems if you're calling a method that expects a certain data type. The sidebar "Data Type Conversion" covers converting SQL Server data types to Visual Basic (VB) data types.

Error Handling

As mentioned earlier, an HRESULT value of 0 means the procedure call was successful. Any other HRESULT value means an error occurred. To make sense of nonzero HRESULT values, you can pass the HRESULT value to stored procedure sp_OAGetErrorInfo with the following syntax:

sp_OAGetErrorInfo [objecttoken] [, source   OUTPUT] [, description OUTPUT] [, helpfile   OUTPUT] [, helpid OUTPUT]

The first parameter is the offending object's objecttoken, which sp_OACreate returns. The next four parameters return error information. Source is the application or library that raised the error. Description is a description of the error. Helpfile is the path to the Help file, if any, associated with the source. These three parameters are all char or varchar, and sp_OAGetErrorInfo truncates the returned values to fit the size of the variable you've declared. The last parameter, helpid, is the Help file context ID for the particular error. The following statement calls sp_OAGetErrorInfo for more information about an error:

Declare @Source varchar(100), @Description   varchar(255), @HelpFile varchar(260),@HelpID intExec sp_OAGetErrorInfo @Object, @Source    OUTPUT, @Description OUTPUT, @HelpFile   OUTPUT, @HelpID OUTPUT

BOL also provides a sample stored procedure called sp_DisplayOAErrorInfo, which calls sp_OAGetErrorInfo for you and combines the returned values into formatted strings that you can output as messages or write to a log. For more information about this procedure, see the sidebar "Using sp_DisplayOAErrorInfo."

In addition, if you need to shut down SQL Server's COM automation environment, you can call stored procedure sp_OAStop, which takes no parameters. Shutting down the automated environment usually isn't necessary. The environment starts up when you call sp_OACreate the first time and shuts down automatically when SQL Server does. Calling sp_OAStop from one procedure while another procedure is automating an object causes the automation in progress to fail and raises errors. I don't recommend calling sp_OAStop programmatically; you should call it only from a query window when you're debugging a particular process that isn't working.

COM Automation in the Real World

Now that you've seen how to use each COM automation stored procedure, let's look at an example that ties them together. Listing 1 shows a procedure called sp_OpenWordIfCoProcAvailable, which uses sp_OACreate to create an instance of Microsoft Word, then uses sp_OAGetProperty to check Word's MathCoProcessorAvailable property. If sp_OAGetProperty returns 1 (true), sp_OpenWordIfCoProcAvailable returns the Word object's object token to the calling procedure. Otherwise, sp_OpenWordIfCoProcAvailable closes Word and returns 0. To save space, I show only the first call to the error handler; you should call the error handler after each call to an automation procedure. Note that to automate Word, you must have it installed on the same machine as SQL Server.

If you're automating custom-built COM objects that you created in Visual Basic (VB), debugging their interaction with SQL Server is fairly easy. All you do is install VB on the same machine that is running SQL Server, load your COM project into the VB editor, set some breakpoints, then compile and run the COM object. When a stored procedure automates your object, the editor will switch to debug mode as it hits a breakpoint, letting you interactively debug the object just as you would any other VB application. For even more control, you can use the T-SQL Debugger add-in for VB, which lets you step through the stored procedure code.

What else can you do with COM automation in SQL Server? Here are some real-world examples of how I've used SQL Server's powerful COM automation feature. A while ago, I needed to connect from a SQL Server stored procedure to a Windows NT service that communicates through named pipes. But SQL Server has no mechanism for programmatically opening and using named pipes. However, I had a VB library of routines that encapsulated the named pipe communication, so I wrapped up the library in a class and created an ActiveX DLL. I then automated the DLL from my stored procedure and, voila, I had named pipe communication.

Another time, I needed to replicate some database tables and some files. Using SQL Server's replication features to replicate the data was easy enough, but moving the files was more difficult. NT's directory synchronization features are weak and didn't meet my needs. Although I could have placed copy statements into varchar variables and passed them to xp_cmdshell, I would have faced command-line length limitations. Furthermore, that approach wouldn't give me a good way to determine what went wrong in case an error occurred during the copy. So, I wrote and automated another ActiveX DLL to handle the file copying.

Finally, I needed to perform a heterogeneous query within SQL Server 6.5 that performed a join with an Index Server 2.0 catalog. Performing such a task is easy if you're using SQL Server 7.0 and Windows 2000 Indexing Services along with ADO. But before these services existed, I had to do things the hard way. First, I wrote an ActiveX DLL that implemented ixsso.dll, the Index Server query objects. I automated the DLL, which pulled information from the Index Server catalog and returned it to the stored procedure through a method. I then placed the data in a temporary table and performed a join against it. Again, COM automation solved the problem.

COM automation through stored procedures lets you perform nearly any task you want—and all from within SQL Server. According to what I've seen in SQL Server 2000 beta 2, the COM automation functionality remains unchanged. So any code you write today should continue to work a long way down the road.