Resources » Code Snippets » SQL

How to run DOS command within SQL Stored procedure using master..xp_cmdshell


Posted Date: 07-May-2009  Last Updated:   Category: SQL    
Author: Member Level: Gold    Points: 15



It's often desired to run some kind of batch or other or regular file or DOS commands from within SQL Stored procedure.

Here how you can achieve it.

First you have to enable the advance options to run special SP that allows you to run DOS command.


// make sure you have admin rights on the SQL database
EXEC sp_configure 'show advanced options', 1

GO
-- We must run below sql command, to update the currently configured value for advanced options.
RECONFIGURE

GO
-- To enable the stored procedure which runs DOS command. (option or feature)
EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for above feature/SP/option we need to run below command again.
RECONFIGURE

GO


once you run above sql statements, it you are ready to test your command shell Stored Procedure.
We can call it as shown in this example. I am showing one command which copies a file


Declare @src as varchar(500)
Declare @dest as varchar(500)

set @src = 'c:\mytest\source.txt'
set @dest = 'c:\mytest\source_copy.txt'

Declare @doscmd as varchar(500)

-- copy the file without showing any prompts
set @doscmd = 'copy /Y ' + @src + @dest


EXEC master..xp_cmdshell @doscmd

Go


The above sql statements will copy file to it's destination , you can use this feature for many purpose, specially when you want to run a batch file or trigger some kind of server applications

hope it helps...


Did you like this resource? Share it with your friends and show your love!

Responses to "How to run DOS command within SQL Stored procedure using master..xp_cmdshell "
Author: Rik Hess    28 Jul 2009Member Level: Bronze   Points : 1
Two questions:
1. Does it matter which database I am in to run the first script? Should I "USE master" first?

2. Should I include the first script in a Stored Procedure to insure it is set properly for any user?

Thank you.



Author: Rik Hess    28 Jul 2009Member Level: Bronze   Points : 1
Two questions:
1. Does it matter which database I am in to run the first script? Should I "USE master" first?

2. Should I include the first script in a Stored Procedure to insure it is set properly for any user?

Thank you.



Author: hitesh    03 Aug 2009Member Level: Gold   Points : 2
Ans.1: NO you select the DB and open a new editor query window and then you have to have Admin rights for that DB (e.g. dbowner rignts). then you can run the first part.

Ans.2: That's only one time job for any DB for a particular admin user, as I said only Admin can run the first script so if you will try to run it thru any other user, it won't



Author: Abhay Kumar Raut    14 Aug 2009Member Level: Gold   Points : 1
Hi hitesh,

good one .

keep it up.

contribute more.

Thanks and Regards
Abhay



Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Sajib Barua
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India