Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Using Automation Stored Procedures to Access an Object's Properties

This solution describes the SQL Server OLE automation stored procedures and how you can use them to access an object's properties.


advertisement
QL Server comes with a powerful language called Transact-SQL (T-SQL) for writing queries and stored procedures. T-SQL is a full-featured language. Like any programming language, it has command statements, conditional logic, and a variety of functions. But what if T-SQL doesn't have a function that you need? Or what if you have data that you need in SQL Server but is only accessible through a Visual Basic component (DLL)? This is where the SQL Server OLE automation stored procedures can save the day. In this 10-Minute Solution I cover these various stored procedures and how you can use them to access an object's properties. (I'll cover calling an object's methods in a future article.)

SQL Server has seven stored procedures that allow you to create and use ActiveX objects from within SQL Server (see Table 1). These seven stored procedures are actually extended stored procedures and are contained in the stdole70.dll file. Here we'll look at sp_OACreate, sp_OAGetProperty, and sp_OADestroy.



Table 1. OLE Automation Stored Procedures Available in SQL Server

Stored Procedure

Description

sp_OACreate

Creates an object reference in SQL Server to an ActiveX component

sp_OAGetProperty

Calls an object property and returns its value

sp_OASetProperty

Calls an object property and sets its value

sp_OAMethod

Calls an object's method and returns its value; parameter values can also be passed into the method

sp_OADestroy

Destroys an object reference created by sp_OACreate

sp_OAGetErrorInfo

Returns any errors generated by calling the OLE automation stored procedures

sp_OAStop

Halts the OLE automation environment on the server

In order to call an object's methods or properties, that object must first be registered on the server. To register an object, call the regsvr32 executable from the command prompt with the specified file name like this: regsvr32 objectname.dll.

To access any registered ActiveX components from inside SQL Server, your first step is to create the object using sp_OACreate and passing it the ProgID of the object you wish to create. This stored procedure has a return value that indicates its success in creating the object.

The following code uses the Application object from the Microsoft Word object model to demonstrate the use of these stored procedures. Specifically, this code returns the username for the currently installed version of Word. This code can be copied into Query Analyzer and run as long as you have Word installed on the server:

DECLARE @Object int DECLARE @Hresult int DECLARE @Username varchar(255) DECLARE @ErrorSource varchar (255) DECLARE @ErrorDesc varchar (255) --Create the object EXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT --Call the object's property and return the value EXEC @Hresult = sp_OAGetProperty @Object, 'UserName', @Username OUT --Destroy the object EXEC @Hresult = sp_OADestroy @Object IF @Hresult <> 0 BEGIN EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT PRINT "Error Occurred Calling Object: " + @ErrorSource + " " + @ErrorDesc RETURN END PRINT @Username

When you use the OLE Automation stored procedures, the first thing you need to do is create variables for the return value and the object. In this case, the @Object variable holds a reference to Word's Application object. Once the object is created using sp_OACreate, the sp_OAGetProperty stored procedure uses that object reference and calls the specified property. The value of the UserName property is returned to the @UserName variable. This variable must be declared with the appropriate datatype that is returned by the property. The sp_OASetProperty procedure works in the same way but takes a parameter with the value to set the property to.

If an error is encountered, sp_OAGetErrorInfo returns the error information for the last OLE automation stored procedure call. This stored procedure returns the error number, error source, error description, help filename, and help context ID for the current error. The above code uses only the error source and description values.

This gives you a summary of how to use these stored procedures to create an ActiveX object and access its properties. For more detailed information on these procedures and their parameters see the SQL Server BOL.



   
Dianne Siebold is a consultant specializing in Visual Basic and SQL Server programming. She is also the author of the "Visual Basic Developer's Guide to SQL Server" (Sybex). Reach her by e-mail here.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date