Mohammed Atef’s Technical blog

Running windows command shell using xp_cmdshell

Introduction

In this post i am going to share with you how to use xp_cmdshell stored procedure for running windows command shell.

xp_cmdshell is system stored procedure that used to run Windows command shell and passes in a string for execution. Any output is returned as rows of text.

Synatx

xp_cmdshell { ‘command_string’ } [ , no_output ]

where command_string Is the string that contains a command to be passed to the operating system. command_string is varchar(8000) or nvarchar(4000) and no_output Is an optional parameter, specifying that no output should be returned to the client.

Enable XP_cmdshell

If you tried to run this stored procedure and you have error says, access denied, you have to enable xp_cmdshell using below SQL script.

   1: EXEC
   2: sp_configure 'show advanced options', 1
   3: GO
   4: RECONFIGURE
   5: GO
   6: EXEC
   7: sp_configure 'xp_cmdshell', 1
   8: GO
   9: RECONFIGURE
  10: GO

Executing vb application without parameter

If you have a .net windows console application named helloworld.exe and it requires to run it from SQL you can use below list of SQL script

   1: declare @cmd varchar(500)
   2: select @param1=rolename,@param2=solutionname from roles where id in
   3: set @cmd='cmd /C "C:\helloworld.exe'
   4: exec xp_cmdshell  @cmd

Executing vb application with two parameters

otherwise, if you have an exe requires two parameters to run it, you can use below SQL Script.

   1: declare @cmd varchar(500)
   2: declare @param1 varchar(50)
   3: declare @param2 varchar(50)
   4: select @param1='param1',@param2='param2'
   5: set @cmd='cmd /C "C:\helloworld.exe '+@param1+' '+@param2+'"'
   6: exec xp_cmdshell  @cmd

Conclusion

above post we saw how to use xp_cmdshell with different modes of needs, hope it is helped.

Advertisements

May 30, 2013 - Posted by | .Net 2010, C#,VB.Net, Developement | , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: