- Introduction
- General concepts
- OLE Automation Stored Procedures
- sp_OACreate
- sp_OADestroy
- sp_OAGetProperty
- sp_OASetProperty
- sp_OAMethod
- sp_OAGetErrorInfo
- 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 that 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 all 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.
See this link for more information
(here you can find sp_displayoaerrorinfo stored procedure)
"OLE Automation Return Codes and Error Information":
http://msdn.microsoft.com/library/psdk/sql/8_qd_12_25.htm
OLE Automation Stored Procedures
SQL Server supports seven OLE Automation Stored Procedures:
sp_OACreate
sp_OADestroy
sp_OAGetProperty
sp_OASetProperty
sp_OAMethod
sp_OAGetErrorInfo
sp_OAStop
You can find the description of these stored procedures below.
sp_OACreate
First of all, you should call sp_OACreate stored procedure to create
an instance of the OLE object. You should pass two parameters into
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 (for SQL Server 7.0):
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 'SQLDMO.SQLServer'.
See this link for more information
"sp_OACreate (T-SQL)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz.htm
sp_OADestroy
This stored procedure can be used to destroy a created OLE object.
If you don't call sp_OADestroy, the created OLE object will be
destroyed automatically when the batch completes execution.
This is the example:
-- 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)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz_1.htm
sp_OAGetProperty
You can use sp_OAGetProperty stored procedure to get a property value
of an OLE object.
See this link for more information
"sp_OAGetProperty (T-SQL)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz_3.htm
sp_OASetProperty
You can use 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)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz_5.htm
sp_OAMethod
You can use 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:
-- 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)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz_4.htm
sp_OAGetErrorInfo
This stored procedure can be used to display OLE Automation error
information, when error occurs, as sp_displayoaerrorinfo stored
procedure.
However, easier to use 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)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz_2.htm
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)":
http://msdn.microsoft.com/library/psdk/sql/sp_oa-oz_6.htm
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:
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).
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
|
Literature
1. SQL Server Books Online
2. Learning About OLE Automation Stored Procedures
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7910
3. OLE Automation Return Codes and Error Information
http://msdn.microsoft.com/library/psdk/sql/8_qd_12_25.htm
4. INF: Protocol Error in TDS Stream Causes Function Sequence Error
from OLE Automation SPs
http://support.microsoft.com/support/kb/articles/Q243/8/99.ASP
5. OLE Automation Sample Script
http://msdn.microsoft.com/library/psdk/sql/8_qd_12_28.htm
6. BUG: Sp_OA Procedures May Produce "Bad Variable Type" or
"Type Mismatch" Error
http://support.microsoft.com/support/kb/articles/Q173/8/48.ASP
7. PRB: How to Use sp_displayoaerrorinfo in the sp_OA Samples
http://support.microsoft.com/support/kb/articles/Q151/6/02.asp
8. INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object
http://support.microsoft.com/support/kb/articles/Q152/8/01.asp
9. INF: How Sp_OA Procedures Extension to SQL Server Is Implemented
http://support.microsoft.com/support/kb/articles/Q180/7/80.ASP
10.INF: Enabling DLL-Based COM Object Execution Outside SQL Server
http://support.microsoft.com/support/kb/articles/Q198/8/91.ASP
»
See All Articles by Columnist Alexander Chigrik
» 
Comment and Contribute
