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...
|
| Author: Rik Hess 28 Jul 2009 | Member 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 2009 | Member 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 2009 | Member 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 14 Aug 2009 | Member Level: Diamond Points : 1 |
Hi hitesh,
good one .
keep it up.
contribute more.
Thanks and Regards
Abhay
|