Guru's Guide to SQL Server Architecture and Internals, The

Automating SQL-DMO by Using ODSOLE

Not surprisingly, a common use of the sp_OA procs is to automate COM objects exposed by SQL Server itself. Popular choices are the DTS object model and SQL-DMO. In this section, I'll present a couple of stored procedures that show how to automate the SQL-DMO COM objects using SQL Server's ODSOLE facility. Keep in mind that most DMO operations require a SQL Server connection, so stored procedures that control DMO objects via ODSOLE usually have to set up a loop-back connection. Loop-back connections can be problematic when they hold locks or attempt to access resources locked by the calling spid. You can run into situations where you effectively block yourself—your calling spid holds a lock on a resource that ...