In last month I received few emails emails regarding SQL SERVER – Enable xp_cmdshell using sp_configure.
The questions are
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?
I really like the follow up questions of my posts/articles. Answer is xp_cmdshell can execute shell/system command, which includes batch file.
1) Example of running system command using xp_cmdshell is SQL SERVER – Script to find SQL Server on Network
EXEC master..xp_CMDShell 'ISQLÂ -L'
2) Example of running batch file using T-SQL
i) Running standalone batch file (without passed parameters)
EXEC master..xp_CMDShell 'c:findword.bat'
ii) Running parameterized batch file
DECLARE @PassedVariable VARCHAR(100)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = 'SqlAuthority.com'
SET @CMDSQL = 'c:findword.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL
Book Online has additional examples of xp_cmdshell
A. Returning a list of executable files
B. Using Windows net commands
C. Returning no output
D. Using return status
E. Writing variable contents to a file
F. Capturing the result of a command to a file
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL
66 Comments. Leave new
Hi Pinal,
Please suggest me how to export data to Excel or CSV file format using bcp with Column Names
Hi,
Is there any way to run .bat files in sql without using xp_cmdshell??
Please suggest me a way out for running .exe and .bat files inside the stored procedure without using xp_cmdshell.
hi,
i need to change my filename in exact location of it in bulk. i want an uniqueness in the filename. i have seven with different names and extension are .docx,.pdf . for example, eye reoport.docx ,i want to rename it as Eye Report.docx. i have totally 24500 records in sequel. kindly tell me if any one find the solution for this.
Thanks
hi,
i want to know is there any command through which we can automatically open sql server account with username and password at particular time suppose at 9.0 clock and execute select query automatically ?
thanks
but how to set every 15 minutes batchfile call in sql
how to call automatic batchfile in every 15minutes using sql
Hi Dave.. As per my original question about xp_cmdshell.. is there any sql query which will forbid us from using xp_cmdshell command. In my case, previously I was using xp_cmdshell to run batch file, but later requirement changed and I had to execute batch file without using xp_cmdshell. Please suggest if there is a way of doing so.
xp_cmdshell ‘dtexec.exe /F “C:\Cube\ResRentals.dtsx”‘………runns forever and never stops…..Can anyone help me out here please!!!
Andile i had the same issue,You have to check path,
I use ‘””C:\xxxxxxx\xxxxx\anything.dtsx””‘ and works fine.
Any one got a solution related to bat file?
when iam trying to execute the batch from SSMS its saying path not found
EXEC master..xp_cmdshell ‘D:db\Tools\catal.bat’;
GO
The system cannot find the path specified.The path is fine and file exist there.
I am trying to run a simple ‘Select count(*) from dbo.table’ using SQLCMD and output to a file. But along with the records count I am getting the same query which I executed in the output file. How can I make sure that only the output is written to output file.
can you share the command and output.
I’ve tried this steps but I’ve got the following error message. I’m using SQL trigger on database table and this is my code for calling my batch file
DECLARE @Param1 VARCHAR(100);
DECLARE @Param2 VARCHAR(100);
DECLARE @CMDSQL VARCHAR(1000);
SET @Param1 = ‘1234’;
SET @Param2 = ‘5678’;
SET @CMDSQL = ‘x:TestMe.bat’ + @Param1 + ‘ ‘ + @Param2;
EXEC master..xp_CMDShell @CMDSQL;
com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
kindly Help
Can you capture profiler and get SQL error?
Is there a way to call Batch file with Admin priviledge using xp_CMDShell?
How can I execute a batch file from T-SQL stored procedure, but batch file being on a remote server ?
Hi Pinal,
We have scheduled the job for .bat file and it’s running fine. But at some time it stays in executing mode and in that case we do stop/disable the job and enable again.
Please help.
Hi Pinal,
I wanna run standalone batch file with ‘run as administrator’ rights.