Calling COM Objects From T-SQL

By Sean Baird on 13 September 2000 | Tags: Stored Procedures

Dan (and Nathan, and a few others) writes: "I've heard/read that you can call a COM Object from within a stored proc. Could you explain how this is done?"

Yup. This is fairly straightforward; check it out.

The functions mentioned here are all well documented in SQL Server Books Online, so I'm not going to go into a lot of detail. If you have further questions, you know where to send them :)

Calling a COM object from T-SQL is pretty easy. First, you call sp_OACreate to create an instance of the object. This stored proc returns an integer value (a handle) that you'll need later.

Next, you call sp_OAMethod to invoke a method of the object. sp_OAGetProperty will retrieve the value of a property from the object; conversely, sp_OASetProperty will set the value of one of the object's properties.

While you're calling these stored procs to manipulate the object, you use sp_OAGetErrorInfo after each call to see if the object threw an error. Error handling for COM object calls is mostly a manual process, so be prepared to do a lot of typing :)

Once you're done using the object, you call sp_OADestroy to free the object. Objects that aren't explicitly freed are automatically freed by SQL Server at the end of the query batch.

Since COM and SQL Server data types don't match up exactly, I suggest you check out the topic titled "Data Type Conversions Using OLE Automation Stored Procedures" in Books Online.

Books Online also has a topic titled "OLE Automation Sample Script" that has a nice example of calling all of these procedures.

-SQLGuru