OLE Automation
|
|
No good discussion of advanced stored procedures would be complete without at least a brief discussion of OLE automation. This topic is generally foreign to DBAs, unless they have a programming background. OLE automation allows you to create COM objects that can be instantiated from T-SQL using the sp_OA system stored procedures.
COM objects allow you to either create your own program or call another vendor's program from a common interface. One commonly used object is the FileSystem object. This object allows you to create and write to files. It also allows you to create, delete, and copy files into directories. You can use this object as an alternative to using xp_cmdshell. You could even use the OLE automation stored procedures to create and execute DTS packages.
| Note | T-SQL is optimized for queries but lacks functionality for interacting with outside programs. These OLE automation stored procedures give you a method for easily interacting with outside programs. Microsoft has promised to include scripting capabilities other than T-SQL (such as VBScript and JScript) in a future release of SQL Server. |
Creating Objects
To create the object, you can use the sp_OACreate stored procedure and the procedure program, or class ID, and variable:
sp_OACreate progid, | clsid, objecttoken OUTPUT [ , context ]
The class ID is nearly impossible to remember, so most people use the program ID. (For instance, the program ID for Excel is Excel.Application.) To execute a DTS package, use the Package object (DTS.Package). For example, the base syntax for creating an instance of the DTS package object looks like this:
Declare @Object int Declare @hr int Exec @hr= sp_OACreate 'DTS.Package', @Object OUTPUT
In actuality, you would want to add error checking into any OLE automation action. You can use the sp_displayoaerrorinfo stored procedure to capture core error information. In the following example, I intentionally misspelled the DTS.Package object. This causes the creation of the object to fail, and the sp_displayoaerrorinfo stored procedure traps the error. The @hr variable represents the return code of the object. If the object returns anything other than 0, you have received an error. My code traps and displays an error when one occurs:
 |
OLE automation opens the door for a number of potential problems. Only members of the SysAdmin role can create instances of COM objects using the sp_OA stored procedures. This reduces your risks, but does not eliminate them. Any registered DLL file is available to SQL Server through these stored procedures. This means that a user who has rights to these stored procedures could have access to many programs that he or she would normally not have access to.
Although this is a cool feature, use it sparingly. Extending SQL Server through COM could cause regressions that are unpredictable, so test your work extensively before deployment. Make sure that you also monitor the execution of these stored procedures regularly. When the sp_OACreate stored procedure is first executed, it appears in the SQL Server error log with the following error:
Using 'odsole70.dll' version '2000.80.194' to execute extended stored procedure 'sp_OACreate'.
|
DECLARE @object int DECLARE @hr int DECLARE @source varchar(255), @Desc varchar(255) EXEC @hr = sp_OACreate 'DTS.Pakage', @object OUTPUT IF @hr <> 0 BEGIN PRINT '* Could not create the package object *' EXEC sp_displayoaerrorinfo @object, @source OUT, @Desc OUT SELECT convert(varbinary(4),@hr) as hr, @source as Source, @Desc as Description RETURN END
This code outputs the following results to the client:
* Could not create the package object * hr Source Description --------- -------------------------- ------------------------- 2147221005 ODSOLE Extended Procedure Invalid class string (1 row(s) affected)
Place the error checking code after each sp_OA stored procedure action. For the sake of brevity, I'll leave it out of future examples in this section.
Methods
Now that you have a DTS package object created, let's go ahead and load a sample package from a file, using a method. A method is just a type of action.
For example, a method in DTS is loading the package. To load a package stored locally on the SQL Server, use the LoadFromSQLServer method in the DTS object model. To call this method from a stored procedure, use the sp_OAmethod system stored procedure as shown here:
EXEC @hr = sp_OAmethod @Object,'LoadFromSQLServer ("(local)", "sa", "password", , , , , "Hello World")', NULL | Note | Many examples of this type of OLE automation can be found at http://www.sqlservercentral.com/scripts/ . My favorite is one by Clinton Herring, who shows you how to send mail using OLE automation and SMTP. This can be downloaded at http://www.sqlservercentral.com/scripts/contributions/510.asp . |
In DTS, just because the package is loaded doesn't mean the package has executed. You must explicitly state that you would like to execute the package. In a moment, I'll execute the package, but first I want to read and set some information in the package.
Properties
Unlike methods, properties are items that can be read, and sometimes written to. For example, a property in DTS may be a catalog name into which you're loading data. In this example, I'm going to read the number of global variables that are in this package, using the sp_OAgetproperty system stored procedure as shown here:
EXEC @hr = sp_OAgetproperty @object, 'GlobalVariables.Count', @gvCount Output Print 'GlobalVariables in package: ' + @gvCount
This same type of property could be used to determine if a file exists in a directory, using the FileSystem object.
The PRINT statement outputs the following results to the client:
GlobalVariables in package: 1
You can also set properties using the sp_OAsetproperty system stored procedure. For example, I can set a global variable in the Hello World package before I execute the package. The Hello World package has a single ActiveX Script task that displays the value of the global variable named gvClientMessage. Whatever I set that value to here is displayed in a popup message. I'm setting it to 'New Message' due to a general lack of creativity:
EXEC @hr = sp_OAsetproperty @object, 'GlobalVariables("gvClientMessage").Value', 'New Message' I usually use this type of code to set a global variable's value that represents a client number or filename to transform. After you have all the information set and read, you're ready to execute the package again with the sp_OAmethod stored procedure and the Execute method as shown here:
EXEC @hr = sp_OAmethod @Object, 'Execute'
Once you execute the package, you see these results.
| Note | A package like this waits for you to click OK before the query finishes. Avoid interfacing with programs that require human interaction as I developed here. This example was merely a demonstration of seeing the results of setting a property. |
Cleaning Up
It is not required that you clean up your objects when using the sp_OA stored procedures, but it's always good programming to do so. If you don't close the objects properly, SQL Server destroys the object when the batch completes. To destroy the object manually, you can use sp_OAdestroy as shown here:
EXEC @hr = sp_OAdestroy @Object
You can also call the sp_OAstop stored procedure to stop the OLE automation services. This frees up resources if you're not regularly executing sp_OA stored procedures. If you are regularly executing them, don't stop the services. Once stopped, the OLE automation services automatically restart the next time one of the sp_OA stored procedures is executed. To stop the services, use the following syntax without any parameters:
EXEC sp_OAstop
Now that you have the full picture, you can piece together the code given in this part of the chapter and execute the entire process. Don't forget to add error handling into each sp_OA call. Make sure you customize the error handling to fit your needs. For example, use the Print command to output a defined reason to the client about why a query fails.
Alternate Method of Counting Records
Once your tables grow to millions of records, just counting the number of records in a table can be cumbersome and take a long time. For example, think about a table that stores Web site traffic. You would normally retrieve the number of records in the database by using the count() function as shown in this code:
SET STATISTICS IO ON GO select count(*) from PageHits go
Turning on statistics lets you see the amount and type of work the SQL Server is performing. This query outputs the following:
----------- 1395300 (1 row(s) affected) Table 'PageHits'. Scan count 1, logical reads 4397, physical reads 2, read-ahead reads 3922.
You can see here that the SQL Server has to work hard to perform this count. Logical reads occur when a page is already cached, while a physical read causes
the page to be written into cache. If you look at the SHOWPLAN (discussed in Chapter 7), you'll notice that it took a table scan to obtain this count.
It is a little-known fact that the current amount of rows for any table is located
in the sysindexes table in the Rows column. If you use the following query, you'll obtain the same results, but with far less I/O:
SET STATISTICS IO ON GO SELECT rows FROM sysindexes WHERE id = OBJECT_ID('PageHits') AND indid < 2 GO This results in the following:
rows ----------- 1395300 (1 row(s) affected) Table 'sysindexes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
| Caution | The rows column is updated when SQL Server generates statistics. On most systems, this is accurate, but you may want to experiment to make sure this truly represents an accurate number. If your statistics are kept up to date, you should have no problem. |
|
|
