Home arrow SQL Server arrow Page 5 - Extended Stored Procedures: Intro And 10 Cool Examples

Extended Stored Procedures: Intro And 10 Cool Examples

In this article Joe takes us through what an SQL Server 2000 extended stored procedure is. He also shows us how to use 10 of SQL Server 2000's most coolest extended stored procedures to accomplish tasks such as killing a system process, writing to the event log, encrypting data to a database, parsing XML, shelling to the command-line, and more.

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 134
February 11, 2002
  1. · Extended Stored Procedures: Intro And 10 Cool Examples
  2. · Extended Stored Procedures
  3. · Useful Extended Stored Procedures
  4. · Examples (contd.)
  5. · Examples (contd.)
  6. · Conclusion

print this article

Extended Stored Procedures: Intro And 10 Cool Examples - Examples (contd.)
(Page 5 of 6 )

Proc #8: xp_logevent

Have you ever wanted to log a user-defined message to SQL Servers 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 Servers 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
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials