19 OLE Automation
The paperless office is about as likely as the paperless bathroom.
—Joe Celko
SQL Server provides a set of stored procedures whose purpose is to work with automation (formerly known as OLE automation) objects. Automation provides a language-independent means of controlling and using objects exposed by other programs. For example, you can use automation to instruct Word to spell check a document or Excel to compute a formula. A good number of programs and tools expose pieces of themselves to the outside world through automation objects. If you have access to an automation controller, you can make use of those objects to manipulate the host application. Fortunately, you do have access to such a controller—SQL Server’s ODSOLE facility, which is exposed via a set of system procedures that you can call from Transact-SQL.
The Transact-SQL stored procedures that relate to automation are named using the convention sp_OA Function where Function indicates what the procedure does (e.g., sp_OACreate instantiates automation objects, sp_OAMethod calls an automation method, sp_OAGetProperty and sp_OASetProperty get and set properties). This facility adds a considerable amount of power to the Transact-SQL language. Anything you can get at via an automation interface, you can manipulate with Transact-SQL.
To illustrate how this works, I’ll show you a stored procedure that uses automation to fill a gap in the Transact-SQL arsenal. You may recall that Transact-SQL has a BULK INSERT command. Its purpose is to bulk load an operating system file via SQL Server’s bulk copy interface. Unfortunately, there’s no reciprocating syntax for exporting data. You’d think they would have provided a BULK EXPORT command for exporting data to operating system files, but that’s not the case. Of course, there are a number of ways around this that don’t involve Transact-SQL. For example, you could use the bcp.exe command-line utility to perform data exports. You could build an automation controller using a traditional development tool such as Visual Basic or Delphi. You could even use Enterprise Manager to perform your exports. But none of these alternatives would be nearly as much fun as exporting data directly from Transact-SQL vis-à-vis BULK INSERT.
The examples that follow will illustrate how to use the automation stored procedures to automate objects exposed by the server itself—the Distributed Management Objects (SQL-DMO). These objects provide much of Enterprise Manager’s underlying functionality and are a handy way of managing the server via program code. Note that you aren’t limited to accessing automation objects exposed by SQL Server—you can manipulate automation objects exposed by any application—Access, Visio, Visual C++, etc.
Given that SQL Server provides a robust automation interface to the bulk copy facility in SQL-DMO, we can build Transact-SQL code that performs bulk exports using Transact-SQL and the automation procedures mentioned above. Here’s a script that builds a stored procedure to do just that:
USE master
GO
IF (OBJECT_ID(’sp_exporttable’) IS NOT NULL)
DROP PROC sp_exporttable
GO
CREATE PROC sp_exporttable
@table varchar(128), -- Table to export
@outputpath varchar(128)=NULL, -- Output directory, terminate with a ""
@outputname varchar(128)=NULL, -- Output filename (default @table+’.BCP’)
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- User name to use (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
/*
Object: sp_exporttable
Description: Exports a table in a manner similar to BULK INSERT
Usage: sp_exporttable
@table varchar(128), -- Table to export
@outputpath varchar(128)=NULL, -- Output directory, terminate with a ’’
@outputname varchar(128)=NULL, -- Output filename (default @table+’.BCP’)
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- User name to use (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
Returns: Number of rows exported
Created by: Ken Henderson. Email: [email protected]
Example: EXEC sp_exporttable "authors"
Created: 1999-06-14. Last changed: 1999-07-14.
*/
AS
IF (@table=’/?’) OR (@outputpath IS NULL) GOTO Help
DECLARE @object int, -- Work variable for instantiating automation objects
@hr int, -- Contains HRESULT returned by automation
@bcobject int, -- Stores pointer to BulkCopy object
@TAB_DELIMITED int, -- Will store a constant for tab-delimited output
@logname varchar(128), -- Name of the log file
@errname varchar(128), -- Name of the error file
@dbname varchar(128), -- Name of the database
@rowsexported int -- Number of rows exported
SET @TAB_DELIMITED=2 -- SQL-DMO constant for tab-delimited
exports
SET @dbname=ISNULL(PARSENAME(@table,3),DB_NAME()) -- Extract the DB name; default to current
SET @table=PARSENAME(@table,1) -- Remove extraneous stuff from table name
IF (@table IS NULL) BEGIN
RAISERROR(’Invalid table name.’,16,1)
GOTO Help
END
IF (RIGHT(@outputpath,1)<>’’)
SET @outputpath=@outputpath+’’ -- Append a "" if necessary
SET @logname=@outputpath+@table+’.LOG’ -- Construct the log file name
SET @errname=@outputpath+@table+’.ERR’ -- Construct the error file name
IF (@outputname IS NULL)-- Construct the output name based on export table
SET @outputname=@outputpath+@table+’.BCP’
ELSE
IF (CHARINDEX(’’,@outputname)=0)
SET @outputname=@outputpath+@outputname
-- Create a SQLServer object
EXEC @hr=sp_OACreate ’SQLDMO.SQLServer’, @object OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Create a BulkCopy object
EXEC @hr=sp_OACreate ’SQLDMO.BulkCopy’, @bcobject OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s DataFilePath property to the output file name
EXEC @hr = sp_OASetProperty @bcobject, ’ataFilePath’, @outputname
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Tell BulkCopy to create tab-delimited files
EXEC @hr = sp_OASetProperty @bcobject, ’ataFileType’, @TAB_DELIMITED
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s LogFilePath property to the log file name
EXEC @hr = sp_OASetProperty @bcobject, ’LogFilePath’, @logname
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s ErrorFilePath property to the error file name
EXEC @hr = sp_OASetProperty @bcobject, ’ErrorFilePath’, @errname
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Connect to the server
IF (@password IS NOT NULL)
EXEC @hr = sp_OAMethod @object, ’Connect’, NULL, @server, @username, @password
ELSE
EXEC @hr = sp_OAMethod @object, ’Connect’, NULL, @server, @username
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer to the SQLServer object’s Databases collection
EXEC @hr = sp_OAGetProperty @object, ’atabases’, @object OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer from the Databases collection for the specified database
EXEC @hr = sp_OAMethod @object, ’Item’, @object OUT, @dbname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer from the Database object’s Tables collection for the table
IF (OBJECTPROPERTY(OBJECT_ID(@table),’IsTable’)=1) BEGIN
EXEC @hr = sp_OAMethod @object, ’Tables’, @object OUT, @table
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
END ELSE
IF (OBJECTPROPERTY(OBJECT_ID(@table),’IsView’)=1) BEGIN
EXEC @hr = sp_OAMethod @object, ’Views’, @object OUT, @table
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
END ELSE BEGIN
RAISERROR(’Source object must be either a table or view.’,16,1)
RETURN -1
END
-- Call the object’s ExportData method to export the table/view using BulkCopy
EXEC @hr = sp_OAMethod @object, ’ExportData’, @rowsexported OUT, @bcobject
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
RETURN @rowsexported
Help:
EXEC sp_usage @objectname=’sp_exporttable’,
@desc=’Exports a table in a manner similar to BULK INSERT’,
@parameters="
@table varchar(128), -- Table to export
@outputpath varchar(128)=NULL, -- Output directory, terminate with a ’’
@outputname varchar(128)=NULL, -- Output filename (default @table+’.BCP’)
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- User name to use (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
",
@author=’Ken Henderson’, @email=’[email protected]’,
@datecreated=’19990614’,@datelastchanged=’19990714’,
@example=’EXEC sp_exporttable "authors", "C:TEMP"’,
@returns=’Number of rows exported’
RETURN -1
GO
Follow the comments in the source code to see how the procedure works—it’s fairly straightforward. Once the procedure is created, you can run it using this syntax:
DECLARE @rc int
EXEC @rc=pubs..sp_exporttable @table=’pubs..authors’,
@outputpath=’:\_tempcp’
SELECT RowsExported=@rc
RowsExported
------------
23
Note the use of the “pubs..” prefix when calling the stored procedure. The stored procedure makes use of the OBJECTPROPERTY() function, which does not work across databases. Therefore, for the procedure to work correctly with objects in other databases, you need to change the current database context temporarily to match the one passed in via @table. As mentioned elsewhere in this book, prefixing a system procedure call with a database name temporarily changes the database context. It’s the functional equivalent of:
USE pubs
GO
EXEC @rc=sp_exporttable @table=’pubs..authors’, @outputpath=’:\_tempcp’
GO
USE master -- or some other database
GO
Also note the use of the sp_displayoaerrorinfo system procedure. This procedure isn’t created by default, but you can find the source to it in the Books Online. It relies on sp_hexadecimal, which is also available in the Books Online. See the topic “OLE Automation Return Codes and Error Information” under the sp_OAGetErrorInfo stored procedure in the Books Online for the source code to both procedures.
The tasks the procedure must accomplish are as follows:
1. Create a SQLServer object and log into the server. All communication with the server via SQL-DMO happens through this connection.
2. Create a BulkCopy object and set its properties to reflect the type of bulk copy operation we want to perform. We’ll call the Table object’s ExportData method to do the actual data export, but it requires a BulkCopy object in order to perform the operation.
3. Locate the source database by extracting its name from @table and looking it up in the SQLServer object’s Databases collection.
4. Locate the source table/view by looking it up in the Database object’s Tables or Views collection.
5. Call the object’s ExportData method, passing it the BulkCopy object that was previously created.
6. Return an integer indicating the number of rows exported. Return −1 in case of an error.
Using automation, the procedure is able to perform all these tasks with relative ease. The amount of Transact-SQL code required to do this is no more than that required by a comparable Delphi or Visual Basic program.
Even though Transact-SQL provides the BULK INSERT command for bulk loading data, for completeness, here’s the bulk load counterpart to sp_exporttable:
USE master
GO
IF (OBJECT_ID(’sp_importtable’) IS NOT NULL)
DROP PROC sp_importtable
GO
CREATE PROC sp_importtable
@table varchar(128), -- Table to import
@inputpath varchar(128)=NULL, -- input directory, terminate with a ""
@inputname varchar(128)=NULL, -- input filename (defaults to @table+’.BCP’)
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- Name of the user to connect as (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
/*
Object: sp_importtable
Description: Imports a table similarly to BULK INSERT
Usage: sp_importtable
@table varchar(128), -- Table to import
@inputpath varchar(128)=NULL, -- input directory, terminate with a ’’
@inputname varchar(128)=NULL, -- input filename (defaults to @table+’.BCP’)
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- Name of the user to connect as (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
Returns: Number of rows imported
Created by: Ken Henderson. Email: [email protected]
Example: EXEC importtable "authors", "C:TEMP"
Created: 1999-06-14. Last changed: 1999-07-14.
*/
AS
IF (@table=’/?’) OR (@inputpath IS NULL) GOTO Help
DECLARE @object int, -- Work variable for instantiating automation objects
@hr int, -- Contains HRESULT returned by automation
@bcobject int, -- Stores pointer to BulkCopy object
@TAB_DELIMITED int, -- Will store a constant for tab-delimited input
@logname varchar(128), -- Name of the log file
@errname varchar(128), -- Name of the error file
@dbname varchar(128), -- Name of the database
@rowsimported int -- Number of rows imported
SET @TAB_DELIMITED=2 -- SQL-DMO constant for tab-delimited
imports
SET @dbname=ISNULL(PARSENAME(@table,3),DB_NAME()) -- Extract the DB name; default to current
SET @table=PARSENAME(@table,1) -- Remove extraneous stuff from table name
IF (@table IS NULL) BEGIN
RAISERROR(’Invalid table name.’,16,1)
RETURN -1
END
IF (RIGHT(@inputpath,1)<>’’)
SET @inputpath=@inputpath+’’ -- Append a "" if necessary
SET @logname=@inputpath+@table+’.LOG’ -- Construct the log file name
SET @errname=@inputpath+@table+’.ERR’ -- Construct the error file name
IF (@inputname IS NULL)
SET @inputname=@inputpath+@table+’.BCP’ -- Construct the input name based on import table
ELSE
SET @inputname=@inputpath+@inputname -- Prefix source path
-- Create a SQLServer object
EXEC @hr=sp_OACreate ’SQLDMO.SQLServer’, @object OUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Create a BulkCopy object
EXEC @hr=sp_OACreate ’SQLDMO.BulkCopy’, @bcobject OUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s DataFilePath property to the input file name
EXEC @hr = sp_OASetProperty @bcobject, ’ataFilePath’, @inputname
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Tell BulkCopy to create tab-delimited files
EXEC @hr = sp_OASetProperty @bcobject, ’ataFileType’, @TAB_DELIMITED
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s LogFilePath property to the log file name
EXEC @hr = sp_OASetProperty @bcobject, ’LogFilePath’, @logname
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s ErrorFilePath property to the error file name
EXEC @hr = sp_OASetProperty @bcobject, ’ErrorFilePath’, @errname
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Set BulkCopy’s UseServerSideBCP property to true
EXEC @hr = sp_OASetProperty @bcobject, ’UseServerSideBCP’, 1
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @bcobject, @hr
RETURN
END
-- Connect to the server
IF (@password IS NOT NULL)
EXEC @hr = sp_OAMethod @object, ’Connect’, NULL, @server, @username, @password
ELSE
EXEC @hr = sp_OAMethod @object, ’Connect’, NULL, @server, @username
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer to the SQLServer object’s Databases collection
EXEC @hr = sp_OAGetProperty @object, ’atabases’, @object OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer from the Databases collection for the specified database
EXEC @hr = sp_OAMethod @object, ’Item’, @object OUT, @dbname
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer from the Database object’s Tables collection for the specified table
IF (OBJECTPROPERTY(OBJECT_ID(@table),’IsTable’)<>1) BEGIN
RAISERROR(’Target object must be a table.’,16,1)
RETURN -1
END BEGIN
EXEC @hr = sp_OAMethod @object, ’Tables’, @object OUT, @table
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
END
-- Call the Table object’s importData method to import the table using BulkCopy
EXEC @hr = sp_OAMethod @object, ’ImportData’, @rowsimported OUT, @bcobject
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
RETURN @rowsimported
Help:
EXEC sp_usage @objectname=’sp_importtable’,
@desc=’Imports a table similarly to BULK INSERT’,
@parameters="
@table varchar(128), -- Table to import
@inputpath varchar(128)=NULL, -- input directory, terminate with a ’’
@inputname varchar(128)=NULL, -- input filename (defaults to @table+’.BCP’)
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- Name of the user to connect as (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
",
@author=’Ken Henderson’, @email=’[email protected]’,
@datecreated=’19990614’,@datelastchanged=’19990714’,
@example=’EXEC importtable "authors", "C:TEMP"’,
@returns=’Number of rows imported’
RETURN -1
GO
Like BULK INSERT, sp_importtable loads operating system files into tables. As with sp_exporttable, it makes certain assumptions about the format of the file that you may change if you wish. Here’s a code sample that uses sp_exporttable and sp_importtable together:
SET NOCOUNT ON
USE pubs
DECLARE @rc int
-- First, export the rows
EXEC @rc=pubs..sp_exporttable @table=’pubs..authors’, @outputpath=’:\_tempcp’
SELECT @rc AS RowsExported
-- Second, create a new table to store the rows
SELECT * INTO authorsimp FROM authors WHERE 1=0
-- Third, import the exported rows
EXEC pubs..sp_importtable @table=’authorsimp’,
@inputpath=’:\_tempcp’,@inputname=’authors.bcp’
SELECT COUNT(*) AS RowsLoaded FROM authorsimp
GO
DROP TABLE authorsimp
This script begins by exporting the authors table from the pubs sample database. It then creates an empty copy of the table and imports the exported rows using sp_importtable. As with BULK INSERT, the file it loads must be directly accessible by the machine on which SQL Server is running.
In addition to bulk load operations, SQL Server’s SQL-DMO interface provides access to a wealth of administration services and server information. Much of this is exposed via automation objects. One such object is the Registry object. It provides access to the portion of the system registry controlled by SQL Server. You can use it to access such things as the currently installed character set, the default SQL Mail login name, the number of processors, the amount of memory installed on the server computer, and so on. Here’s a stored procedure that gives you access to the bevy of information provided by the Registry object:
USE master
GO
IF OBJECT_ID(’sp_getSQLregistry’) IS NOT NULL
DROP PROC sp_getSQLregistry
GO
CREATE PROC sp_getSQLregistry
@regkey varchar(128), -- Registry key to extract
@regvalue varchar(8000)=NULL OUTPUT, -- Value from SQL Server registry tree for key
@server varchar(128)=’(local)’, -- Name of the server to connect to
@username varchar(128)=’sa’, -- Name of the user to connect as (defaults to ’sa’)
@password varchar(128)=NULL -- User’s password
/*
Object: sp_getSQLregistry
Description: Retrieves a value from the SQL Server branch in the system registry
Usage: sp_getSQLregistry
@regkey varchar(128), -- Registry key to extract
@regvalue varchar(8000) OUTPUT, -- Value from SQL Server registry tree for key
@server varchar(128)="(local)", -- Name of the server to connect to
@username varchar(128)="sa", -- Name of the user to connect as (Default: "sa")
@password varchar(128)=NULL -- User’s password
Returns: Data length of registry value
Created by: Ken Henderson. Email: [email protected]
Version: 6.4
Example: sp_getSQLregistry "SQLRootPath", @sqlpath OUTPUT
Created: 1996-09-03. Last changed: 1999-07-01.
*/
AS
SET NOCOUNT ON
IF (@regkey=’/?’) GOTO Help
DECLARE @object int, -- Work variable for instantiating automation objects
@hr int -- Contains HRESULT returned by automation
-- Create a SQLServer object
EXEC @hr=sp_OACreate ’SQLDMO.SQLServer’, @object OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Connect to the server
IF (@password IS NOT NULL) AND (@password<>’’
EXEC @hr = sp_OAMethod @object, ’Connect’, NULL, @server, @username, @password
ELSE
EXEC @hr = sp_OAMethod @object, ’Connect’, NULL, @server, @username
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer to the SQLServer object’s Registry object
EXEC @hr = sp_OAGetProperty @object, ’Registry’, @object OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a pointer to the SQLServer object’s Databases collection
EXEC @hr = sp_OAGetProperty @object, @regkey, @regvalue OUT
IF @hr <> 0 BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
RETURN datalength(@regvalue)
Help:
EXEC sp_usage @objectname=’sp_getSQLregistry’,
@desc=’Retrieves a value from the SQL Server branch in the system registry’,
@parameters=’
@regkey varchar(128), -- Registry key to extract
@regvalue varchar(8000) OUTPUT, -- Value from SQL Server registry tree for key
@server varchar(128)="(local)", -- Name of the server to connect to
@username varchar(128)="sa", -- Name of the user to connect as (Default: "sa")
@password varchar(128)=NULL -- User’’ password’,
@author=’Ken Henderson’, @email=’[email protected]’,
@datecreated=’19960903’, @datelastchanged=’19990701’,
@version=’6’, @revision=’4’,
@returns=’ata length of registry value’,
@example=’sp_getSQLregistry "SQLRootPath", @sqlpath OUTPUT’
GO
Here’s a script that uses sp_getSQLregistry to access key system information:
SET NOCOUNT ON
DECLARE @numprocs varchar(10), @installedmemory varchar(20), @rootpath varchar(8000)
EXEC sp_getSQLregistry ’PhysicalMemory’, @installedmemory OUT
EXEC sp_getSQLregistry ’NumberOfProcessors’, @numprocs OUT
EXEC sp_getSQLregistry ’SQLRootPath’, @rootpath OUT
SELECT @numprocs AS NumberOfProcessors, @installedmemory AS InstalledRAM, @rootpath AS
RootPath
DECLARE @charset varchar(100), @sortorder varchar(100)
EXEC sp_getSQLregistry ’CharacterSet’, @charset OUT
SELECT @charset AS CharacterSet
EXEC sp_getSQLregistry ’SortOrder’, @sortorder OUT
SELECT @sortorder AS SortOrder
NumberOfProcessors InstalledRAM RootPath
------------------ ------------ -----------------------------------
1 79 c:MSSQL7
CharacterSet
----------------------------------------------------------------------------------------
Character Set = 1, iso_1 ISO 8859-1 (Latin-1) -- Western European 8-bit character set.
SortOrder
------------------------------------------------------------------------------------
Sort Order = 52, nocase_iso Case-insensitive dictionary sort order for use with
several We
Transact-SQL’s ability to interface with automation objects allows it to perform the kinds of tasks usually reserved for traditional development tools such as Delphi and Visual Basic. In this chapter, you explored manipulating automation objects that the server itself exposes—the Distributed Management Objects—but Transact-SQL’s automation procedures (sp_OAxxxx) aren’t limited to automation objects exposed by the server. You can automate Excel, Word, Visio, or any application that provides an automation interface—all from within Transact-SQL.