Atlanta Custom Software Development 

   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Working with COM objects from within T-SQL

Total Hit ( 1546)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


This article includes

- Introduction
- General concepts
- OLE Automation Stored Procedures
  1. sp_OACreate
  2. sp_OADestroy
  3. sp_OAGetProperty
  4. sp_OASetProperty
  5. sp_OAMethod
  6. sp_OAGetErrorInfo
  7. sp_OAStop

- Example: generate script
- Literature

--------------------------------------------------------------------------------

Introduction

In this article, I want to tell you about how you can work with COM objects from within Transact SQL. You can use OLE Automation Stored Procedures (extended stored procedures with sp_OA prefix) to create a COM object in T-SQL and use the object's methods and properties.


General concepts

The OLE Automation Stored Procedures, in other words, sp_OA procedures (where OA stands for OLE Automation), allow a connection, through T-SQL commands, to create and use Component Object Model (COM) based objects. These procedures are built into SQL Server as an extended stored procedure.

Each OLE Automation stored procedure returns an integer code that is the HRESULT returned by the underlying OLE Automation operation. If HRESULT is equal to 0, then everything is okay, a nonzero HRESULT indicates OLE error (in hexadecimal format).

You can use the sp_displayoaerrorinfo stored procedure to display OLE Automation error information (error description, not only hexadecimal code), when error occurs. This very useful procedure is not installed by default, so you can create it manually from SQL Server Books Online.


OLE Automation Stored Procedures

SQL Server supports seven OLE Automation Stored Procedures:
  1. sp_OACreate
  2. sp_OADestroy
  3. sp_OAGetProperty
  4. sp_OASetProperty
  5. sp_OAMethod
  6. sp_OAGetErrorInfo
  7. sp_OAStop

You can find the description of these stored procedures below.


sp_OACreate

First of all, you should call the sp_OACreate stored procedure to create an instance of the OLE object. You should pass two parameters into the sp_OACreate stored procedure: program ID or class ID and variable. The variable will be a reference to the OLE object for the further using by other sp_OA stored procedures.

This is the example:

Click here to copy the following block
DECLARE @object int
DECLARE @hr int

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
 EXEC sp_displayoaerrorinfo @object, @hr
 RETURN
END

Note If you use SQL Server 6.5, you must write 'SQLOLE.SQLServer' instead of 'SQLDMO.SQLServer'.

See this link for more information: sp_OACreate (T-SQL)


sp_OADestroy

This stored procedure can be used to destroy the created OLE object. If you don't call the sp_OADestroy, the created OLE object will be destroyed automatically when the batch completes execution.

This is the example:

Click here to copy the following block
-- Destroy the previously created SQL Server object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
 EXEC sp_displayoaerrorinfo @object, @hr
 RETURN
END

See this link for more information: sp_OADestroy (T-SQL)

sp_OAGetProperty

You can use the sp_OAGetProperty stored procedure to get a property value of an OLE object.

See this link for more information: sp_OAGetProperty (T-SQL)

sp_OASetProperty
You can use the sp_OASetProperty stored procedure to set a property of an OLE object to a new value.

See this link for more information: sp_OASetProperty (T-SQL)

sp_OAMethod
You can use the sp_OAMethod stored procedure to call a method of an OLE object.

This example calls the VerifyConnection method of the previously created SQL Server object:

Click here to copy the following block
-- Verify the connection
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0
BEGIN
 EXEC sp_displayoaerrorinfo @object, @hr
 RETURN
END

See this link for more information: sp_OAMethod (T-SQL)

Click here to copy the following block
sp_OAGetErrorInfo

This stored procedure can be used to display the OLE Automation error information when error occurs.

However, easier to use the sp_displayoaerrorinfo stored procedure instead of sp_OAGetErrorInfo, because sp_displayoaerrorinfo is a wrapped stored procedure for sp_OAGetErrorInfo.

See this link for more information: sp_OAGetErrorInfo (T-SQL)

sp_OAStop

This stored procedure can be used to stop the OLE Automation Stored Procedures execution environment.

The execution environment will automatically restart the next time you call sp_OACreate stored procedure.

See this link for more information: sp_OAStop (T-SQL)

Example: generate script

This script will generate script for all tables and all dependent objects for the given database. You can pass the server name, user name, user password, database name and file name into sp_GenerateScript stored procedure, as in the example below:

Click here to copy the following block
EXEC sp_GenerateScript @server = 'Server_Name',
            @uname = 'User_Name',
            @pwd = 'Password',
            @dbname = 'Database_Name',
            @filename = 'c:\File_Name.sql'

You can specify different number of parameters (from zero to five parameters).
If you not specify server name, then the current server will be used;
if you not specify database name, then the current database will be used;
if you use Windows NT Authentication mode, then you should not specify username and password;
if you not specify user name, but specify password, then the current user name will be used;
if you not specify password, then password will not be used (for example: username = 'sa' and empty password, you can pass only username in this case);
if you not specify file name, then script will be placed into file script.sql on the drive 'c:'.

This stored procedure can be used for learning some general SQL Server features (how to work with OLE objects from the SQL Server, how to use some system functions, how to work with cursors and so on).

Note : sp_displayoaerrorinfo is a user defined SP to display COM errors. Refer to the following article for more information.

How to display COM error description in SQL Stored procedure ?

Click here to copy the following block
if object_id('sp_GenerateScript') is not null drop proc sp_GenerateScript
GO

CREATE PROC sp_GenerateScript (
 @server varchar(30) = null,
 @uname varchar(30) = null,
 @pwd varchar(30) = null,
 @dbname varchar(30) = null,
 @filename varchar(200) = 'c:\script.sql'
)
AS

DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(200)
DECLARE @exec_str varchar(200)
DECLARE @tbname varchar(30)

SET NOCOUNT ON

-- Set the server to the local server
IF @server is NULL
 SELECT @server = @@servername

-- Set the database to the current database
IF @dbname is NULL
 SELECT @dbname = db_name()

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
 EXEC sp_displayoaerrorinfo @object, @hr
 RETURN
END

-- Connect to the SQL Server
IF (@uname is NULL) AND (@pwd is NULL)
 BEGIN
  -- Windows NT Authentication mode is used
  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server
  IF @hr <> 0
   BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
   END
 END
ELSE
IF (@uname is NULL)
 BEGIN
  -- Set the username to the current user name
  SELECT @uname = SYSTEM_USER
  EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@uname,@pwd
  IF @hr <> 0
   BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
   END
 END
ELSE
IF (@pwd is NULL)
 BEGIN
  EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @server, @uname
  IF @hr <> 0
   BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
   END
 END
ELSE
 BEGIN
  EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@uname,@pwd
  IF @hr <> 0
   BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
   END
 END

-- Verify the connection
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0
BEGIN
 EXEC sp_displayoaerrorinfo @object, @hr
 RETURN
END
SET @exec_str = "DECLARE script_cursor CURSOR FOR SELECT name FROM "
       + @dbname + "..sysobjects WHERE type = 'U' ORDER BY Name"
EXEC (@exec_str)

OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @tbname
WHILE (@@fetch_status <> -1)
BEGIN
 SET @exec_str = 'Databases("'+ @dbname +'").Tables("'
         + RTRIM(UPPER(@tbname))+'").Script(74077,"'
         + @filename +'")'
 EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
 IF @hr <> 0
  BEGIN
   EXEC sp_displayoaerrorinfo @object, @hr
   RETURN  
  END
 FETCH NEXT FROM script_cursor INTO @tbname
END
CLOSE script_cursor
DEALLOCATE script_cursor
 
-- Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
 EXEC sp_displayoaerrorinfo @object, @hr
 RETURN
END
GO



Audit, Notify, Deploy and Manage SSIS
Download the Free 30-day Trial Version...Learn More About This Product...

Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  Blogs

© 2008 BinaryWorld LLC. All rights reserved.