One of the most frequent questions that I see in the newsgroup is "how can I access command line tools like DIR, NET SEND etc" from a SQL Server procedure. In this article we are going to see just that. SQL Server supports an extended stored procedure called "xp_cmdshell" that resides in the "master" database. This command executes a given command string as an operating-system command shell and returns any output as rows of text. The following is the syntax of the command:
xp_cmdshell '<>'[, no_output]
The only required parameter is the command string. The "no_output" argument is an optional argument that is used to not return any rows to the client. Also, note that xp_cmdshell operates synchronously. Control is not returned until the command shell command completes. By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.
Ok, enought of the basics. Let's see something in action. The Windows operating system supports a command called NET SEND that can be used to send popup messages to specific machines on the network (or to the whole domain). Let's see how this command can be called from within a stored procedure. Create the following stored procedure:
IF (OBJECT_ID ('[NET SEND]') IS NOT NULL)
DROP PROCEDURE [NET SEND]
GO
CREATE PROCEDURE [NET SEND] (@tcMessage VARCHAR(8000)) AS
BEGIN
DECLARE @command VARCHAR(8000)
SET @command = 'master..xp_cmdshell ''net send ' + @tcMessage + ''''
EXEC (@command)
END
Once this procedure is created, you can call it like this from the Query Analyzer window. If you are executing it from a stored procedure, you will want to prefix the EXEC statement before the call.
[NET SEND] 'pc-srinivas How are you??'
Note that I'm specifying the computer name (pc-srinivas) as required by the command. The output of this call, is the following popup window (which comes in the my desktop).
Well, that's a useful script!! You can use it to send messages to people when some work has been done or needs attention. One note of caution though. You cannot use xp_cmdshell to execute any program that has a user interface that waits for user input.