SQL Server and COM Automation

for RuBoard

SQL Server provides a set of stored procedures that allows you to automate COM objects from Transact-SQL. Automation is a language-independent method of controlling and using COM objects. Lots of applications expose functionality via COM objects. Many of Microsoft's retail products, as well as many from other vendors , expose some type of functionality via COM objects. You can use these objects to manipulate the host application through an automation controllera facility that knows how to interact with COM interfaces. The most popular automation controller is Visual Basic, followed closely by VBScript. SQL Server's ODSOLE facility is an automation controller in its own right and is exposed via a set of system procedures that you can access from Transact-SQL.

The sp_OA Procedures

Transact-SQL's automation stored procedures are named using the convention sp_OA Function, where Function indicates what the procedure does (for example, sp_OACreate creates COM objects, sp_OAMethod calls a method, sp_OAGetProperty and sp_OASetProperty get and set object properties, and so on). These procedures make Transact-SQL considerably more powerful because they give you full access to the world of COM.

sp_checkspelling

The code that follows illustrates a simple procedure that uses the sp_OA procedures to automate a COM object. The procedure instantiates the Microsoft Word Application object and calls its CheckSpelling method to check the spelling of a word you pass the procedure. Here's the code:

 USE master GO IF (OBJECT_ID('sp_checkspelling') IS NOT NULL)   DROP PROC sp_checkspelling GO CREATE PROC sp_checkspelling        @word varchar(30),        -- Word to check         @correct bit OUT         -- Returns whether word is correctly spelled /* Object: sp_checkspelling Description: Checks the spelling of a word using the Microsoft Word Application automation object Usage: sp_checkspelling        @word varchar(128),       -- Word to check        @correct bit OUT          -- Returns whether word is correctly spelled Returns: (None) $Author: Ken Henderson $. Email: [e-mail] Example: EXEC sp_checkspelling 'asdf', @correct OUT Created: 2000-10-14. $Modtime: 2001-01-13 $. */ AS IF (@word='/?') GOTO Help DECLARE @object int,       -- Work variable for instantiating COM objects        @hr int      -- Contains HRESULT returned by COM -- Create a Word Application object EXEC @hr=sp_OACreate 'Word.Application', @object OUT IF (@hr <> 0) BEGIN     EXEC sp_displayoaerrorinfo @object, @hr     RETURN END -- Call its CheckSpelling method EXEC @hr = sp_OAMethod @object, 'CheckSpelling', @correct OUT, @word IF (@hr <> 0) BEGIN     EXEC sp_displayoaerrorinfo @object, @hr     RETURN @hr END -- Destroy it EXEC @hr = sp_OADestroy @object IF (@hr <> 0) BEGIN     EXEC sp_displayoaerrorinfo @object, @hr     RETURN @hr END RETURN 0 Help: EXEC sp_usage @objectname='sp_checkspelling', @desc='Checks the spelling of a word using the Microsoft Word Application automation object', @parameters='        @word varchar(30),        -- Word to check         @correct bit OUT         -- Returns whether word is correctly spelled ', @author='Ken Henderson', @email='[e-mail]', @datecreated='20001014',@datelastchanged='20010113', @example='EXEC sp_checkspelling ''asdf'', @correct OUT', @returns='(None)' RETURN -1 GO 

Sp_checkspelling exposes two parametersthe word whose spelling you wish to check and an output parameter to receive a 1 or 0 indicating whether the word is spelled correctly. A call to the procedure looks like this:

 DECLARE @cor bit EXEC sp_checkspelling 'asdf', @cor OUT SELECT @cor 

(Results)

 ---- 0 

There are three key elements of this procedure: the creation of the COM object, the method call, and the disposal of the object. Let's begin with the call to sp_OACreate. Calling sp_OACreate instantiates a COM object. "Word.Application" is what is known in COM as a ProgIDa programmatic identifier. A programmatic identifier is a string that identifies a COM object so that applications can access it by name . How do we know to specify Word.Application here? Several ways. First, we could check the Word object interface as documented in MSDN. Second, we could fire up Visual Basic and add a Reference to the Microsoft Word Object Library to a project, then allow Visual Studio's IntelliSense technology to show us the objects and methods available from Word (you can do the same thing via Delphi's ProjectImport Type Library option). Third, we could simply check the system registry and scan for all the interfaces involving Microsoft Word. The registry, for example, tells us that Word.Application is Word's VersionIndependentProgID string. This means that instantiating Word.Application should work regardless of the version of Word that's installed.

We store the object handle that's returned by sp_OACreate in @object. This handle is then passed into sp_OAMethod when we call methods on the Word.Application interface. In this case, we call just one methodCheckSpellingand pass @word as the word for which to check spelling and @correct to receive the 1 or 0 returned by the method.

When we're finished with the object, we destroy it by calling sp_OADestroy. Again, we pass in the @object handle we received earlier from sp_OACreate.

TIP

You can control whether objects created with sp_OACreate run inside the SQL Server process or outside it. COM objects that run inside the SQL Server process are known as in-proc servers; those that run outside are known as out-of-proc servers. When possible, build COM objects that can run out of process (.EXEs). These are far less likely to cause SQL Server itself problems because they run outside the SQL Server process space. When your object is capable of running out-of-process, you can instruct SQL Server to load it out of process using sp_OACreate's third parameter, context. A value of 1 tells SQL Server to load the object in process, 4 says to load it out of process, and 5 says to allow either type of server.

This is what it's like to work with COM objects in Transact-SQL. As with many languages and technologies, you create the object, do some things with it, then clean up after yourself when you're through.

sp_exporttable

Here's another stored procedure that makes use of the sp_OA routines to control a COM object. Sp_exporttable instantiates SQL Server's own SQL-DMO objects to export a table by name. It works analogously to the built-in BULK INSERT command, providing an interface to the BCP API from Transact-SQL. Here's the code:

 USE master GO IF (OBJECT_ID('sp_exporttable') IS NOT NULL)   DROP PROC sp_exporttable GO CREATE PROC sp_exporttable        @table sysname,            -- Table to export        @outputpath sysname=NULL,  -- Output directory, terminate with a "\"        @outputname sysname=NULL,  -- Output filename (defaults to @table+'.BCP')        @server sysname='(local)',        -- Name of the server to connect to        @username sysname='sa',           -- Name of the user (defaults to 'sa')        @password sysname=NULL,           -- User's password        @trustedconnection bit=1          -- Use a trusted connection to connect to the server /* Object: sp_exporttable Description: Exports a table in a manner similar to BULK INSERT Usage: sp_exporttable        @table sysname,            -- Table to export        @outputpath sysname=NULL,  -- Output directory, terminate with a '\'        @outputname sysname=NULL,  -- Output filename (defaults to @table+'.BCP')        @server sysname='(local)',        -- Name of the server to connect to        @username sysname='sa',           -- Name of the user (defaults to 'sa')        @password sysname=NULL,           -- User's password        @trustedconnection bit=1         -- Use a trusted connection to connect to the server Returns: Number of rows exported $Author: Ken Henderson $. Email: [e-mail] Example: EXEC sp_exporttable 'authors', 'C:\TEMP\' Created: 1999-06-14. $Modtime: 2000-12-01 $. */ AS IF (@table='/?') OR (@outputpath IS NULL) GOTO Help DECLARE @srvobject int,        -- Server object        @object int,     -- Work variable for instantiating COM objects        @hr int,         -- Contains HRESULT returned by COM        @bcobject int,          -- Stores pointer to BulkCopy object        @TAB_DELIMITED int, -- Will store a constant for tab-delimited output        @logname sysname,   -- Name of the log file        @errname sysname,   -- Name of the error file        @dbname sysname,    -- 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 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)   SET @outputname=@outputpath+@table+'.BCP' -- Construct the output name ELSE   IF (CHARINDEX('\',@outputname)=0)     SET @outputname=@outputpath+@outputname -- Create a SQLServer object EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @srvobject OUTPUT IF (@hr <> 0) GOTO ServerError -- Create a BulkCopy object EXEC @hr=sp_OACreate 'SQLDMO.BulkCopy', @bcobject OUTPUT IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's DataFilePath property to the output filename EXEC @hr = sp_OASetProperty @bcobject, 'DataFilePath', @outputname IF (@hr <> 0) GOTO BCPError -- Tell BulkCopy to create tab-delimited files EXEC @hr = sp_OASetProperty @bcobject, 'DataFileType', @TAB_DELIMITED IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's LogFilePath property to the log filename EXEC @hr = sp_OASetProperty @bcobject, 'LogFilePath', @logname IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's ErrorFilePath property to the error filename EXEC @hr = sp_OASetProperty @bcobject, 'ErrorFilePath', @errname IF (@hr <> 0) GOTO BCPError -- Connect to the server IF (@trustedconnection=1) BEGIN   EXEC @hr = sp_OASetProperty @srvobject, 'LoginSecure', 1        IF (@hr <> 0) GOTO ServerError   EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server END ELSE BEGIN  IF (@password IS NOT NULL)   EXEC @hr =sp_OAMethod @srvobject,'Connect',NULL,@server, @username, @password        ELSE          EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server, @username END IF (@hr <> 0) GOTO ServerError -- Get a pointer to the SQLServer object's Databases collection EXEC @hr = sp_OAGetProperty @srvobject, 'Databases', @object OUT IF (@hr <> 0) GOTO ServerError -- Get a pointer from the Databases collection for the specified database EXEC @hr = sp_OAMethod @object, 'Item', @object OUT, @dbname IF (@hr <> 0) GOTO Error -- 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) GOTO Error END ELSE -- Get a pointer from the Database object's View collection for the view IF (OBJECTPROPERTY(OBJECT_ID(@table),'IsView')=1) BEGIN   EXEC @hr = sp_OAMethod @object, 'Views', @object OUT, @table   IF (@hr <> 0) GOTO Error 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) GOTO Error EXEC sp_OADestroy @srvobject -- Dispose of the server object EXEC sp_OADestroy @bcobject -- Dispose of the bcp object RETURN @rowsexported Error: EXEC sp_displayoaerrorinfo @object, @hr GOTO ErrorCleanUp BCPError: EXEC sp_displayoaerrorinfo @bcobject, @hr GOTO ErrorCleanUp ServerError: EXEC sp_displayoaerrorinfo @srvobject, @hr GOTO ErrorCleanUp ErrorCleanUp: IF @srvobject IS NOT NULL   EXEC sp_OADestroy @srvobject -- Dispose of the server object IF @bcobject IS NOT NULL   EXEC sp_OADestroy @bcobject  -- Dispose of the bcp object RETURN -2 Help: EXEC sp_usage @objectname='sp_exporttable', @desc='Exports a table in a manner similar to BULK INSERT', @parameters='        @table sysname,                -- Table to export        @outputpath sysname=NULL,   -- Output directory, terminate with a ''\''        @outputname sysname=NULL,   -- Output filename (default: @table+''.BCP'')        @server sysname=''(local)'',-- Name of the server to connect to        @username sysname=''sa'',   -- Name of the user (defaults to ''sa'')        @password sysname=NULL,      -- User''s password         @trustedconnection bit=1     -- Use a trusted connection ', @author='Ken Henderson', @email='[e-mail]', @datecreated='19990614',@datelastchanged='20001201', @example='EXEC sp_exporttable ''authors'', ''C:\TEMP\''', @returns='Number of rows exported' RETURN -1 GO 

Sp_exporttable follows this general plan of attack:

1. Create a SQLServer object. We'll use this object to connect to the server. Most DMO applications require a SQLServer object. We access the other objects on the server by drilling into the SQLServer object just as you do in Enterprise Manager.

2. Create a BulkCopy object. We'll use this object to export the table. Ultimately, we'll call the ExportData method of the specified table or view to "bulk copy" its contents to an operating system file. ExportData requires a BulkCopy object to do its work.

3. Set various properties on the BulkCopy object that will control the export.

4. Connect to the server using the SQLServer object.

5. Locate the table or view to be exported using nested object collections exposed by the SQLServer object.

6. Call the ExportData method of the view or table object, passing it the required BulkCopy object as a parameter

7. Once the export finishes, dispose of the SQLServer and BulkCopy objects.

The comments in the stored procedure detail how it works. It's pretty straightforward. You run sp_exporttable using this syntax:

 DECLARE @rc int EXEC @rc=pubs..sp_exporttable @table='pubs..authors', @outputpath='c:\temp\' SELECT RowsExported=@rc RowsExported ------------ 23 

Note the use of the pubs .. prefix on the procedure call. Because sp_exporttable uses the OBJECTPROPERTY() function (which does not work across databases), in order for the procedure to work correctly with objects in other databases, the database context must be temporarily changed to the correct one for the object specified. As I mentioned earlier in this book, prefixing a system procedure call with a database name temporarily changes the database context. The previous call is the equivalent of:

 USE pubs GO EXEC @rc=sp_exporttable @table='pubs..authors', @outputpath='c:\temp\' GO USE master -- or some other database GO SELECT RowsExported=@rc 

You may have noticed the calls to the sp_DisplayOAErrorInfo system procedure. We use sp_displayoaerrorinfo to display more verbose error info for error codes returned by the sp_OA procedures. Sp_DisplayOAErrorInfo calls sp_OAGetErrorInfo to get extended error info for object automation error codes. Sp_DisplayOAErrorInfo isn't created by default, but you can find it in the Books Online. It depends on sp_hexadecimal (also in the Books Online) to convert binary values to hexadecimal strings. See the topic "OLE Automation Return Codes and Error Information" in the Books Online for the source code to both procedures.

This example and the ones that follow illustrate how to use the sp_OA stored procedures to automate COM objects (SQL-DMO, in this case) exposed by SQL Server itself. These objects provide much of Enterprise Manager's underlying functionality and are a handy way of managing the server via program code. Of course, you aren't limited to accessing objects exposed by SQL Server. You can automate objects exposed by any applicationPowerBuilder, Excel, Oracle, and so forthand can even create your own COM objects and use them with SQL Server.

The comments in sp_exporttable explain how it works. It does a number of interesting things that are too extensive to get into here in detail. Using COM automation, the procedure is able to perform a fairly involved task with ease. The amount of Transact-SQL code required to accomplish the task is no more than that required by a comparable Delphi or Visual Basic program.

sp_importtable

Despite the fact that we have the Transact-SQL BULK INSERT command for bulk loading data, here's the source to sp_importtable, the bulk load counterpart to sp_exporttable for completeness:

 USE master GO IF (OBJECT_ID('sp_importtable') IS NOT NULL)   DROP PROC sp_importtable GO CREATE PROC sp_importtable        @table sysname,            -- Table to import        @inputpath sysname=NULL,   -- input directory, terminate with a "\"        @inputname sysname=NULL,   -- input filename (defaults to @table+'.BCP')        @server sysname='(local)', -- Name of the server to connect to        @username sysname='sa',    -- Name of the user (defaults to 'sa')        @password sysname=NULL,          -- User's password         @trustedconnection bit=1        -- Use a trusted connection /* Object: sp_importtable Description: Imports a table similarly to BULK INSERT Usage: sp_importtable        @table sysname,            -- Table to import        @inputpath sysname=NULL,   -- input directory, terminate with a '\'        @inputname sysname=NULL,   -- input filename (defaults to @table+'.BCP')        @server sysname='(local)', -- Name of the server to connect to        @username sysname='sa',    -- Name of the user (defaults to 'sa')        @password sysname=NULL,           -- User's password        @trustedconnection bit=1         -- Use a trusted connection Returns: Number of rows imported  $Author: Ken Henderson $. Email: [e-mail] Example: EXEC sp_importtable 'authors', 'C:\TEMP\' Created: 1999-06-14. $Modtime: 2000-12-03 $. */ AS IF (@table='/?') OR (@inputpath IS NULL) GOTO Help DECLARE @srvobject int,  -- Server object        @object int, -- Work variable for instantiating COM objects        @hr int,     -- Contains HRESULT returned by COM        @bcobject int,      -- Stores pointer to BulkCopy object        @TAB_DELIMITED int, -- Will store a constant for tab-delimited input        @logname sysname,   -- Name of the log file        @errname sysname,   -- Name of the error file        @dbname sysname,    -- 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 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 ELSE   SET @inputname=@inputpath+@inputname    -- Prefix source path -- Create a SQLServer object EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @srvobject OUT IF (@hr <> 0) GOTO ServerError -- Create a BulkCopy object EXEC @hr=sp_OACreate 'SQLDMO.BulkCopy', @bcobject OUT IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's DataFilePath property to the input filename EXEC @hr = sp_OASetProperty @bcobject, 'DataFilePath', @inputname IF (@hr <> 0) GOTO BCPError -- Tell BulkCopy to create tab-delimited files EXEC @hr = sp_OASetProperty @bcobject, 'DataFileType', @TAB_DELIMITED IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's LogFilePath property to the log filename EXEC @hr = sp_OASetProperty @bcobject, 'LogFilePath', @logname IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's ErrorFilePath property to the error filename EXEC @hr = sp_OASetProperty @bcobject, 'ErrorFilePath', @errname IF (@hr <> 0) GOTO BCPError -- Set BulkCopy's UseServerSideBCP property to TRUE EXEC @hr = sp_OASetProperty @bcobject, 'UseServerSideBCP', 1 IF (@hr <> 0) GOTO BCPError -- Connect to the server IF (@trustedconnection=1) BEGIN   EXEC @hr = sp_OASetProperty @srvobject, 'LoginSecure', 1        IF (@hr <> 0) GOTO ServerError   EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server END ELSE BEGIN        IF (@password IS NOT NULL)          EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server, @username, @password        ELSE          EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server, @username END IF (@hr <> 0) GOTO ServerError -- Get a pointer to the SQLServer object's Databases collection EXEC @hr = sp_OAGetProperty @srvobject, 'Databases', @object OUT IF (@hr <> 0) GOTO ServerError -- Get a pointer from the Databases collection for the specified database EXEC @hr = sp_OAMethod @object, 'Item', @object OUT, @dbname IF (@hr <> 0) GOTO Error -- Get a pointer to 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) GOTO Error 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) GOTO Error EXEC sp_OADestroy @srvobject -- Dispose of the server object EXEC sp_OADestroy @bcobject  -- Dispose of the bcp object RETURN @rowsimported Error: EXEC sp_displayoaerrorinfo @object, @hr GOTO ErrorCleanUp BCPError: EXEC sp_displayoaerrorinfo @bcobject, @hr GOTO ErrorCleanUp ServerError: EXEC sp_displayoaerrorinfo @srvobject, @hr GOTO ErrorCleanUp ErrorCleanUp: IF @srvobject IS NOT NULL   EXEC sp_OADestroy @srvobject -- Dispose of the server object IF @bcobject IS NOT NULL   EXEC sp_OADestroy @bcobject  -- Dispose of the bcp object RETURN -2 Help: EXEC sp_usage @objectname='sp_importtable', @desc='Imports a table similarly to BULK INSERT', @parameters='       @table sysname,            -- Table to import       @inputpath sysname=NULL,   -- input directory, terminate with a ''\''       @inputname sysname=NULL,   -- input filename (default: @table+''.BCP'')       @server sysname=''(local)'', -- Name of the server to connect to       @username sysname=''sa'',  -- Name of the user (defaults to ''sa'')       @password sysname=NULL     -- User''s password ', @author='Ken Henderson', @email='[e-mail]', @datecreated='19990614',@datelastchanged='20001203', @example='EXEC sp_importtable ''authors'', ''C:\TEMP\''', @returns='Number of rows imported' RETURN -1 GO 

Similarly to the built-in BULK INSERT command, sp_importtable loads external files into SQL Server tables. As with sp_exporttable, sp_importtable assumes that file is tab delimited, but you can change this if you wish. Here's an example of sp_exporttable and sp_importtable being used together:

 SET NOCOUNT ON USE pubs DECLARE @rc int -- First, export the rows EXEC @rc=pubs..sp_exporttable @table='pubs..authors', @outputpath='c:\temp\' 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= 'c:\temp\',@inputname='authors.bcp' SELECT COUNT(*) AS RowsLoaded FROM authorsimp GO DROP TABLE authorsimp 

The script starts by exporting the authors table from the pubs sample database to a tab-delimited text file. It creates an empty copy of the table and then imports the exported file using sp_importtable. As with BULK INSERT, any file that sp_importtable is to load must be accessible from the SQL Server machine.

sp_getSQLregistry

SQL Server's SQL-DMO provides access to a wealth of Enterprise Manager-type services and server info. Given that SQL-DMO is a COM-based technology, this functionality is exposed via COM objects. One of these objects is the Registry object. SQL-DMO's Registry object provides access to the portion of the system registry used by SQL Server. You can use this object to get at such things as the default SQL Mail login name, the default SQL Server installation path, the number of processors installed on the server, and so forth. Here's a stored procedure that shows how to use 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 OUT, -- Value from SQL Server registry        @server varchar(128)='(local)',   -- Name of the server to connect to        @username sysname='sa',    -- Name of the user (defaults to 'sa')        @password sysname=NULL,    -- User's password   @trustedconnection bit=1        -- Use a trusted connection /* 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) OUT,  -- Value from SQL Server registry tree        @server varchar(128)="(local)", -- Name of the server to connect to        @username varchar(128)="sa",    -- Name of the user (Default: "sa")        @password varchar(128)=NULL     -- User's password        @trustedconnection bit=1        -- Use a trusted connection Returns: Data length of registry value $Author: Ken Henderson $. Email: [e-mail] $Revision: 6.4 $ Example: sp_getSQLregistry "SQLRootPath", @sqlpath OUT Created: 1996-09-03. $Modtime: 2000-11-14 $. */ AS SET NOCOUNT ON IF (@regkey='/?') GOTO Help DECLARE @srvobject int, -- Server object @object int, -- Work variable for instantiating COM objects @hr int -- Contains HRESULT returned by COM -- Create a SQLServer object EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @srvobject OUTPUT IF (@hr <> 0) GOTO ServerError -- Connect to the server IF (@trustedconnection=1) BEGIN   EXEC @hr = sp_OASetProperty @srvobject, 'LoginSecure', 1        IF (@hr <> 0) GOTO ServerError   EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server END ELSE BEGIN  IF (@password IS NOT NULL)   EXEC @hr =sp_OAMethod @srvobject,'Connect',NULL,@server, @username, @password  ELSE   EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server, @username END IF (@hr <> 0) GOTO ServerError -- Get a pointer to the SQLServer object's Registry object EXEC @hr = sp_OAGetProperty @srvobject, 'Registry', @object OUT IF (@hr <> 0) GOTO Error -- Get the registry value EXEC @hr = sp_OAGetProperty @object, @regkey, @regvalue OUT IF (@hr <> 0) GOTO ServerError EXEC sp_OADestroy @srvobject -- Dispose of the server object RETURN datalength(@regvalue) Error: EXEC sp_displayoaerrorinfo @object, @hr GOTO ErrorCleanUp ServerError: EXEC sp_displayoaerrorinfo @srvobject, @hr GOTO ErrorCleanUp ErrorCleanUp: IF @srvobject IS NOT NULL   EXEC sp_OADestroy @srvobject -- Dispose of the server object RETURN -2 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        @server varchar(128)="(local)", -- Name of the server to connect to        @username varchar(128)="sa",     -- Name of the user (Default: "sa")        @password varchar(128)=NULL      -- User's password', @author='Ken Henderson', @email='[e-mail]', @datecreated='19960903', @datelastchanged='20001114', @version='6', @revision='4', @returns='Data length of registry value', @example='sp_getSQLregistry "SQLRootPath", @sqlpath OUTPUT' GO 

You can use sp_getSQLregistry to retrieve values from the SQL Server portion of the system registry via the SQL-DMO Registry object. Here's an example:

 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 ------------------ -------------------- --------------------------------------- 2                  223                  C:\Program Files\Microsoft SQL Server\MSSQL$SS2000 CharacterSet ------------------------------------------------------------------------------- Latin1-General SortOrder ------------------------------------------------------------------------------- case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Se 

for RuBoard