Calling COM Objects From T-SQL

By Sean Baird on 13 September 2000 | 8 Comments | 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.


Discuss this article: 8 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

An Evaluation of Stored Procedures for the .NET Developer (22 March 2004)

Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts

?????? ??????????? ?????? 2017 (0 Replies)

WITH EXECUTE_AS not working for sproc. (9 Replies)

Data loss during sql cluster failover (3 Replies)

How to make Ajax Accordian Visible based on dropdo (2 Replies)

Corrupt SMALL transaction log backups in Log Shipp (3 Replies)

How to create select statement and Split Data (6 Replies)

unable to shrink mdf file (25 Replies)

Trouble Altering table using SQL-SMO in C#.Net (5 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -