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, @CMVal
	
Exec 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 int

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