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.

-SQLGuru


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

Can this be done in SQL (2 Replies)

how to do this? (9 Replies)

database design example with scenarios and backup (1 Reply)

How do I query for current day? (0 Replies)

Bug fix in sorting/ordering (6 Replies)

SQL exception when field has two words or a space (4 Replies)

MS Access database trouble (1 Reply)

Future Accountant interested in SQL (11 Replies)

Subscribe to SQLTeam.com

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

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -