O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required