Automation and T-SQL

By Bill Wunder

 

T-SQL does 4 things well: select, insert, update, and delete. That’s not to say that you can’t write a select, insert, update or delete that behaves poorly any more than it absolutely limits the use of T-SQL to the primary SQL statements. The reality is that a huge pool of experts have developed an impressively sophisticated query processing engine to assure that the queries against even a moderately complex database perform optimally. For everything beyond the basic quartet of SQL statements the query processor is not going to be able to offer as much under the hood assistance to a T-SQL script or stored procedure. To be sure, T-SQL has steadily improved over the years in areas such as string manipulation, the shell interface, and even table pivot operations: things that, to this day, a ‘real’ programming language or application executable can do better and faster than a T-SQL script or stored procedure. Still, we’ve all found compelling and valid reasons to use the tools we’re given and the rich collection of T-SQL features should not be ignored.

 

One set of T-SQL tools that strays far from traditional SQL into the realm of OLE automation are affectionately know as the sp_OAs. These are a set of seven extended stored procedures steeped in mysticism and fraught with the perils of memory leaks, weak documentation, and the need for T-SQL to actually handle an error. The memory leak issue is important and in my opinion persistent (see Microsofts KB articles Q282229 and Q320130 for examples). If you use a component that leaks memory and it is called 50,000 times a day you likely will have an unstable SQL Server. If you call it 500 times a day you might get away with it if you have a large pool of physical memory and reboot every night . I don’t recommend the sp_OAs for either scenario. However, If you have to roll out a conversion script intended to be executed once on a server or you have a job sequence that must be carefully controlled and occurs relatively infrequently the OLE automation might be just the ticket.

 

As an example: I found myself in a situation where I had three possible versions of a script that I needed to execute during a data conversion. One of my goals for any conversion script is that a single script should make all changes, The idea being that if the person applying the conversion has only the task of initiating the only conversion script there is greater confidence that no part of the conversion is forgotten or missed or fails and no one notices, so having three possible versions was troublesome to me. The factors to determine which script would be appropriate at first seemed quite convoluted. At the beginning everyone fully expected to have to ask an offsite user to manually check several criteria on multiple servers and then select the appropriate script. As we dug into the problem, we discovered that the correct script could be selected based on the version of only two .dlls. Even at that no one was particularly comfortable with the need for a manual check given our concern for the sensitivity of the data and what could happen if someone made a mistake. We had limited time to get a conversion process tested and ready. Using the sp_OAs to get the version of the two files proved to be a snap. Right in the T-SQL script I could decide the appropriate version and could therefore fulfill my objective of making the entire conversion happen in one script.

 

The coolest thing this script illustrates is how easy it is to actually use the automation stored procedures. If you’re at all familiar with ActiveX or shell scripting and programming to an object model then implementing OLE automation with the sp_OAs is a breeze. If you’re not, taking the time to understand what is happening in this simple code segment is going to help prepare you for a future of unlimited possibilities. It is very different from a traditional SQL query! 

 

Since I already knew that the Windows Scripting Host’s FileSystemObject included the GetFileVersion method. All I had to figure out was how to implement the call from T-SQL. Take a quick step through of the solution I used (This works on SQL2K SP3 on WIN2K SP3 server and the .dll referenced in the example should be on any server so feel free to try it out -- in your test or development environment of course!):

 

 

-- determine the fully qualified name(s) of file

declare @FileSpec varchar(255), @Version varchar(15)

set @FileSpec = 'C:\WINNT\system32\ver.dll'

 

-- get all file versions before script makes any changes

declare @oFSO int, @oFile int

declare @hr int

declare @src varchar(255), @desc varchar(255)

exec @hr = sp_OACreate 'Scripting.FileSystemObject', @oFSO OUT

if @hr <> 0 begin

   exec sp_OAGetErrorInfo @oFSO, @src OUT, @desc OUT

   Goto ErrorHandler

end

 

-- execute the GetFileVersion method on the file

exec @hr = sp_OAMethod @oFSO, 'GetFileVersion', @Version OUT, @FileSpec if @hr <> 0 begin

   exec sp_OAGetErrorInfo @oFSO, @src OUT, @desc OUT

   Goto ErrorHandler

end

 

-- Always clean up the OLE server

exec @hr = sp_OADestroy @oFSO

if @hr <> 0 begin

   exec sp_OAGetErrorInfo @oFSO, @src OUT, @desc OUT

   Goto ErrorHandler

end

 

-- caution, closes automation environment for everyone

exec sp_OAStop   

 

-- do some stuff

if @Version = ''

      print 'not there'

if @Version like '3.0%'

      print 'old'

if @Version like '3.1%'

      print 'current'

 

GoTo Done

 

ErrorHandler:

If @oFSO is not null

      exec @hr = sp_OADestroy @oFSO

exec sp_OAStop   

Raiserror('Unable to get version for file %s. error: %u source: %s desc: %s',19,1,@FileSpec, @hr, @src, @desc) with log

 

Done:

 

 

 

 

As you can see, not much in the way of T-SQL fundamentals in this script, yet I was able to take care business with a single T-SQL Script and all the user had to do was open the file in Query Analyzer and hit f5. Just the way we like it. The operations used to implement a COM object using the sp_OAs will always include:

  1. create the object - This will give you access to the object model
  2. execute a method, or access/modify a property
  3. destroy the object you created

SQL Server Books on Line doesn’t do much to demystify the sp_OAs. If you see a possible use for OLE automation or you need to access an object model in your T-SQL work and you learn from examples you can also have a look at the script admin db - table function to get Agent job status I have posted or check out the stored procedures I have used to script out entire servers intended primarily for SQL 7 for some additional working examples. In both of these examples I use the automation procedures to access the DMO object model .

 

I'd also like to show you how easy and powerful object models like DMO are in a DTS ActiveX task or the Windows scripting host, so stay tuned.

 

Bill