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

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 Query performance Call Center data (1d) Looking for on Premises tool to read data from SQL logs and populate data warehouse (1d) Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (1d) Working with multiple WHERE statements (1d) Create a new field value that shows a difference in value from 2 fields (3d) Hierarchy wise Sales Targets (3d) Get the MaxDate in results (5d) Switch Statement within a SSRS SQL Expression (6d)