Using Automation Stored Procedures to Access an Object’s Properties

Using Automation Stored Procedures to Access an Object’s Properties

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



Creates an object reference in SQL Server to an ActiveX component


Calls an object property and returns its value


Calls an object property and sets its value


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


Destroys an object reference created by sp_OACreate


Returns any errors generated by calling the OLE automation stored procedures


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 intDECLARE @Hresult intDECLARE @Username varchar(255)DECLARE @ErrorSource varchar (255)DECLARE @ErrorDesc varchar (255)--Create the objectEXEC @Hresult = sp_OACreate 'Word.Application', @Object OUT --Call the object's property and return the valueEXEC @Hresult = sp_OAGetProperty @Object, 'UserName', @Username OUT--Destroy the object EXEC @Hresult = sp_OADestroy @ObjectIF @Hresult <> 0BEGIN  EXEC sp_OAGetErrorInfo @Object, @ErrorSource OUT, @ErrorDesc OUT  PRINT "Error Occurred Calling Object:  " + @ErrorSource + " " + @ErrorDesc  RETURNENDPRINT @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.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as