Learning About OLE Automation Stored Procedures - 03 Jan 2000

The system stored procedures in SQL Server often yield interesting and unexpected surprises. You can find a system stored procedure to handle nearly any task you need to do. This week, I discovered the OLE automation (sp_OA) stored procedures, which let you instantiate and work with COM objects from within Transact SQL (T-SQL). You can use these procedures to create a COM object in T-SQL and use that object’s methods and properties. For example, you can use the File System object to open and process files, and you can read and write to files by using the File System object directly within T-SQL.

You can call the OLE automation procedure sp_OACreate and pass it a program ID or class ID and variable, and the procedure will instantiate the object for you. The variable will serve as a reference to the object for all further interactions that involve the other sp_OA stored procedures. After you have used sp_OACreate, you can use sp_OASetProperty and sp_OAGetProperty to set and retrieve property values. You use sp_OAMethod to execute an object’s methods.

When your script is finished using an object, you can use sp_OADestroy to destroy the object. If you don’t call sp_OADestroy, the object will be destroyed automatically when the T-SQL batch completes execution.

You can call sp_OAStop to close the OLE Automation services. After you stop these services, they will automatically restart the next time you call one of the sp_OA stored procedures.

You don't want to overuse the OLE automation stored procedures because they might cause performance problems. Also, the procedures might cause stability problems because running objects in the SQL Server process can cause SQL Server to crash. These caveats aside, sp_OA support lets you do things you can’t do another way.