More Registry Manipulations

SQL Server's extended stored procedures extend the power of T-SQL by letting your T-SQL batches interact with the OS and other objects outside the SQL Server database. Last month, I looked at some undocumented extended stored procedures that you can use to work with the registry. Here are seven more extended stored procedures that can add power to your T-SQL code.

7. xp_fileexist

This extended stored procedure lets you test for the existence of an OS file. If the file exists, xp_fileexist returns a 1 in the File Exists column. For example, to check for the existence of a file named C:\autoexec.bat, you'd use the following extended procedure:

xp_fileexist 'c:\ autoexec.bat '

6. xp_enumdsn

You can use xp_enumdsn to get a list of the configured Data Source Names (DSNs) that are available on the system:

EXEC xp_enumdsn

5. xp_sendmail

At times, you might want to send batch error notifications or even result sets by email. If you've configured SQL Mail, you can do both. The following example shows how to use xp_sendmail to send an email message:

EXEC xp_sendmail 'DBA', 'This stored procedure ended in error.'

4. xp_sprintf

Xp_sprintf lets you build a string by substituting variable values for placeholders. This capability lets you build dynamic SQL statements and output messages. The following example shows how to use xp_sprintf to build a simple INSERT statement:

DECLARE @sql_string varchar (255)
EXEC xp_sprintf @sql_string output, 
'INSERT INTO %s VALUES (%s, %s)', 'mytable', '1', '2'

3. xp_logevent

Writing to the event log is a great way to log the status of your T-SQL stored procedures. You can use xp_logevent to write a completion message to the event log:

EXEC xp_logevent 70000,'SQL Process
logged this message' , informational

2. xp_eventlog

If you're using SQL Server 7.0, you can use SQL Server's undocumented xp_eventlog extended stored procedure to return a result set of entries for a specific event log. (Unfortunately, the xp_eventlog extended stored procedure doesn't work in SQL Server 2000.) The following example shows how you can retrieve the contents of the Application log:

xp_eventlog 'Application'

1. xp_cmdshell

The powerful and potentially dangerous xp_cmdshell extended stored procedure lets you execute almost any program from within a T-SQL batch or stored procedure. Because you can use xp_cmdshell to execute nearly any OS command, you should carefully restrict access to it. The following example shows how to use xp_cmdshell to copy the file C:\temp\myws.xls:

EXEC xp_cmdshell 'copy c:\temp\myws.xls c:\temp\myws2.xls'