Monday, June 20, 2005

10 Useful Extended Stored Procedures

10 Useful Extended Stored Procedures

It's time to take a look at 10 extended stored procedures that can help make your coding of SQL procedures 100 times easier. Each extended stored procedure shown below includes an example, and also why you'd want to use that extended stored procedure when you're creating SQL procedures or batches.

Proc #1: xp_fileexist

If you're only interfacing with SQL Server via ADO in a language such as VB or C++, then both of these languages have functions you can use to check whether or not a file exists. However, if you're like me and love to create your SQL procs in 100% TSQL, then you'll be glad to know that you can check whether or not a file exists using the xp_fileexist extended stored procedure. Its signature looks like this:

xp_fileexist filename [, <file_exists INT> OUTPUT]

The second parameter is optional, and can be used to return the procs value into an integer variable. To return a record containing the details of whether or not the file exists, use the following command:

exec master..xp_fileexist 'c:\test.file'

It returns the following record (assuming the file exists):

Results from the xp_fileexist extended stored proc

To return the value of the xp_fileexist extended store procedure into a variable, we do so like this:

declare @exists int

exec master..xp_fileexist 'c:\test.file', @exists output

print @exists

Proc #2: xp_regread

I was actually quite surprised about this extended stored procedure a couple of months back. It reads in a value from the system registry and returns it as a record. Its signature looks like this:

xp_regread root_key, path_key, key_value

As you should know, the registry includes thousands upon thousands of system variable values, program extensions and versions, directory locations, etc. To retrieve the name of my windows partition from the registry I would use xp_regread like this:

master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\Setup', 'SystemPartition'

Proc #3: xp_repl_encrypt

This handy extended stored procedures allows us to one-way encrypt string of data using an SQL Server-defined algorithm. Why would anyone want to encrypt without being able to decrypt you ask? The answer's quite simple actually. In a recent scenario, I had to store credit card numbers in a database. Being the security buff that I am, I felt opposed to doing so, but I was left with no choice: either store the credit card numbers, or forfeit the job.

I stumbled across the xp_repl_encrypt extended stored procedure and used it to store the credit card numbers in a database. It worked extremely well. Here's a quick example of how you could use the xp_repl_encrypt extended store procedure in your daily doings:

create proc sp_TestCCNum

@cardNumber1 varchar(180)


declare @cardNumber2 varchar(180)

set @cardNumber2 = '4111111111111111'

exec master..xp_repl_encrypt @cardNumber1 output

exec master..xp_repl_encrypt @cardNumber2 output

-- @cardNumber1 and @cardNumber2 are now encrypted

if(@cardNumber1 = @cardNumber2)


print 'Numbers are the same'




print 'Numbers are different'



exec sp_TestCCNum '4111111111111111'

The stored procedure shown above is called sp_TestCCNum and accepts one parameter, a credit card number in the form of a string. It checks whether the encrypted version of that number is the same as the encrypted version of a pre-defined credit card number (which you would normally grab from a database). If they are, then it prints out "Numbers are the same". If not, it prints out "Numbers are different".

Proc #4: xp_dirtree

Sometimes it's handy to have access to the directory structure of the file system through SQL Server. The xp_dirtree is a handy extended store procedure that traverses a directory and returns the details of all of its sub-directories as records. Its signature looks like this:

xp_dirtree parent_directory

To test this extended stored procedure, jump to the DOS prompt (Yes, I know it's old school, but I'm an old school kinda guy) and create some directories like this:

C:\>md mydir

C:\>cd mydir

C:\mydir>md subdir1

C:\mydir>md subdir2

C:\mydir>cd subdir1

C:\mydir\subdir1>md subsubdir1

Enter the following line into Query Analyzer:

exec master..xp_dirtree 'c:\mydir'

After running the query, the results in the output window look like this:

Results of the xp_dirtree function

As you can see from the results set above, each sub-directories name is returned along with a depth value, which is relative to the directory passed to the extended stored procedure. For example, if I passed in c:\blah and that directory contained another directory named c:\blah\blah1, then blah1's depth would be one, because it's one directory level deep.

Proc #5: sp_xml_preparedocument

The sp_xml_preparedocument extended stored procedure takes an XML document, parses it using the MSXML2 parser, and provides the parsed document, which is now ready for consumption using SQL Server various other XML-related procedures. The signature of the sp_xml_preparedocument extended stored procedure looks like this:

sp_xml_preparedocument hdoc OUTPUT [, xmltext] [, xpath_namespaces]

The first parameter, hdoc, is an integer handle that is used to identify the XML document to SQL Server. The second parameter, xmltext, is the actual XML document that SQL Server should tell the MSXML2 library to parse, and the third option is a set of OPENXML XPath namespaces that can be used to format the resultant XML document.

To test the sp_xml_preparedocument extended stored procedure, enter the following code into Query Analyzer:

declare @id int

declare @xml varchar(500)

set @xml = '<people><person name="John Doe" age="30"/></people>'

exec sp_xml_preparedocument @id output, @xml

select * from OpenXML(@id, '/people/person')

with(name varchar(50) '@name')

exec sp_xml_removedocument @id

The code above creates a simple XML string containing a root element and one child element. We use the sp_xml_preparedocument extended stored procedure to load and parse the XML string, returning the ID of the internal representation of the XML document to @id.

To actually retrieve data from the XML, we use a normal select statement in combination with the OpenXML function, which takes the ID of the XML to load, as well as an XPath pattern that specifies the hierarchy to extract data from. The "with" expression tells SQL Server how to return the details of the XML, and the sp_xml_removedocument extended stored procedure removes the XML representation referred to by @id from memory.

The output from running the SQL batch shown above looks like this:

Output from the code described above

Proc #6: xp_availablemedia

As you might've guessed, if an SQL Server isn't setup and configured properly, then several extended stored procedures can compromise the stability of that server. The xp_availablemedia extended stored procedure as well as a couple of others can do just that.

The xp_availablemedia extended stored procedure is used to return a list of available storage devices that can be written to. It doesn't require any special signature definitions, because all you have to do to use it is call it directly, with no parameters.

Use the following line to get a list of all available storage devices:

exec master..xp_availablemedia

On my SQL Server, the results set looked like this:

The results of a call to xp_availablemedia

Proc #7: xp_getnetname

The xp_getnetname extended stored procedure returns the NetBIOS name of the current system. It accepts one optional parameter and its signature looks like this:

xp_getnetname [@netname [var]char(MAX_COMPUTERNAME_LENGTH_ OUT]

To print out the NetBIOS name of my SQL Server, I used the xp_getnetname extended stored procedure like this:

declare @netname varchar(20)

exec master..xp_getnetname @netname output

print 'The name of my SQL Server is ' + @netname

On my system, the output from the SQL code above is "The name of my SQL Server is SQLSERVER".

Proc #8: xp_logevent

Have you ever wanted to log a user-defined message to SQL Serve log files so that it becomes visible through the event viewer? With the xp_logevent extended stored procedure you can do just that. The xp_logevent extended stored procedure takes three parameters and its signature looks like this:

xp_logevent {error_number, 'message'} [, 'severity']

The error_number parameter is a user-defined error number greater than 50,000. Its maximum value is 230 – 1. The message parameter is the actual message that will be shown in the event viewer. Lastly, severity is an optional parameter containing one of three character strings that represents how bad the error that occurred was. The three possible values are INFORMATIONAL, WARNING, and ERROR. If you don't include this parameter, the INFORMATIONAL is used.

To add an event to SQL serve log file, use the following code snippet:

exec master..xp_logevent 65000, "Extended stored procedures are cool", INFORMATIONAL

Next, run event viewer (Start -> Programs -> Administrative Tools -> Event Viewer) and click on the application log node. Look at the top couple of lines and find an entry with a source of "MSSQLSERVER"; double-click on it. It should load a property page showing our error message, number, and severity, like this:

Our error in the event viewer

Obviously you wouldn't write stupid error message like the one that we've just written to the log, but you could (and should) use the xp_logevent extended stored procedure to log events relating to critical file-access errors, errors that occur when critical data has been deleted and can't be found, etc.

Proc #9: xp_terminate_process

As many of you will know, each instance of an executable that is currently active is known as a process. When you load a new web browser, it is a process, when you run a DOS prompt, it's a process, etc. Each process on the system has a process identifier, or PID. Using the xp_terminate_process extended stored procedure, we can kill a process either on the local machine, or on a remote machine (assuming that we have the privileges to do so). Let's take a look at how we can use xp_terminate_process to kill an instance of a DOS prompt.

Start by loading a new instance of the DOS prompt (Start -> Run -> "cmd"). Next, load the windows task manager (Start -> Run -> "taskmgr") and look for the DOS prompt window that you just started:

The process of our DOS prompt

Not the PID field, which is the process ID of our DOS prompt instance. Enter the following line into Query Analyzer, replacing the process ID 3812 with the process ID that was displayed in task manager of your system:

exec master..xp_terminate_process 3812

Press F5 or click on the play button to execute the script. Notice how the DOS prompt window is now gone, because its process has been killed by the xp_terminate_process extended stored procedure.

I'm sure that there are at least a couple of people reading this and asking "That's great, but how do I get the process ID for each process running on my system through VB, C++ or ASP?". Well, luckily the Windows API includes several functions that you can use to do just that. Checkout this Microsoft knowledge base article for details on how to do so.

Proc #10: xp_cmdshell

The favourite extended stored procedure of thousands of developers around the world, xp_cmdshell allows us to execute an operating-system command shell and return the output from that shell as rows of text. The signature of xp_cmdshell looks like this:

xp_cmdshell 'command_string'} [, no_output]

The first parameter is the command to execute, and the second, optional parameter tells SQL Server whether or not to output the results of the shell as rows in a recordset. By default it is set to OUTPUT, but to stop the results of xp_cmdshell being shown, use NO_OUTPUT.

Let's create a batch file that we will call using xp_cmdshell. Create a new file named c:\testbat.bat and enter the following code into it:

@echo off

@echo You passed in %1

Now, enter the following code into Query Analyzer and run it:

declare @arg1 varchar(20)

declare @command varchar(50)

set @arg1 = 'Hello'

set @command = 'c:\testbat.bat ' + @arg1

exec master..xp_cmdshell @command

The output in the bottom window of Query Analyzer should look like this:

Output from xp_cmdshell

Het Waghela  (The Technology Blog)

A man's very highest moment is, I have no doubt at all, when he kneels in the dust, and beats his breast, and tells all the sins of his life.

No comments: