Extend SQL Server with COM Automation

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 int
Declare @RetVal int

Exec @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.