Extended Stored Procedures: Intro And 10 Cool Examples

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:



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:



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:


blog comments powered by Disqus