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.

May 30, 2013 Posted by | .Net 2010, C#,VB.Net, Developement | , , , , | Leave a comment

Microsoft Dev Night

Hi all,
I am very glad to invite you to attend this public event ‘Microsoft Dev Night’.

Actually, we will talk about Microsoft SQL Server 2008, Visual Studio 2010 Team System and .Net Framework 4.0.

ISA, I will speak about Visual studio 2010 Team Systems, so kindly find the main topic that I will go throw it.

Visual Studio 2010 Team System

       Design & Architecture

      Parallel Programming

      Quality/ Testing

      Source Code Management

      Debugging

      Project Management

      Web development

If you are interested to know about this topic you are welcome and I will be very happy to see you there.

Regards,

March 24, 2009 Posted by | .Net 2010 | , , , | 1 Comment