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
I tried this but the directory kept defaulting to c:\windows
system32. I couldn’t get this to run from any other directory. Am I missing something?
Ron I am reading some comments here in Journey to SQL Authority because I am having the same problems that you had in 2007. I have only been using t-sql for 3 years but only reciently have i needed to exec a .bat file from T-SQL. I was wondering what you did to solve this problem. James knapp
Sorry.. user error. I think this was working all along. thank you.
Sir,
I have a strored procdure which uses
xp_cmdshell with .bat files to transfer files from sql server(7.0) to excel. But now iss not working. So I have to that manually.
Can u please guide me in activating that.
Regards
Sanjeev
SQL Database Administrator
Hi,
Is there any way that i can call xp_cmdshell within a .bat file?
Error value is the most important thing to me.
Thank you very much.
I want to execute a batch file from SQL server 2005.
I have run the following command in SQL server editor.
use master
master..xp_cmdshell ‘test’
the text.bat file is in c:\WiNDOWS\system32
I have added the path in environ variable.
but still I have got the following error while executing the above command …..
“use master
master..xp_cmdshell ‘test.bat’….operable program or batch file….NULL”
Sorry the error msg is shown as
“‘test.bat’ is not recognized as an internal or external command,….operable program or batch file….NULL”
hi ,
i want to import data from excel to table in sql server 2005
i am using following code
INSERT INTO dbo.IMPORTDATA
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 3.0;Database=D:\venu\queans.xls’, [Sheet1$])
I GOT FOLLOWING ERROR WHEN THE DATA IS IN BULK (>500 ROWS) WHAT CAN I DO
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.
CAN I USE xp_cmdshell ? how ?
Save the .xls file as a comma delimited .txt file and then run something like this:
bulk insert dbo.table
from ‘c:\folder\folder\filename.txt’
with
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRSTROW=2
)
File must be on the SQL Server to work.
Hi All,
Just wondering what would be the best way of running a set of scripts. They basically do some transformation to the data and export those to another database.
Tks,
Robert
Hi All
I am using “master..xp_cmdshell” utility to execute a script and the script will create a db object like procedure or function.
my code goes like this ( i have not given here variable declaration and their values)
below given @cmd value is just sample script, in my actual program the script will have more than 4000 characters
====================================
set @cmd='”ALTER PROCEDURE [dbo].[sp_cust4] AS select top 10 * from Customer”‘
SET @ISQL_Script = ‘isql -S’+@SQLServer+ ‘ -d’ + @DbName + ‘ -U’+@UName+’ -P’+@PWD+’ -Q’ + rtrim(@cmd)
Exec master..xp_cmdshell @ISQL_Script
====================================
The above script is working fine. But for me as the script goes more than 4000 charcaters, i have to use more than one variable
if i tried as below, nut it is throwing error.
Exec master..xp_cmdshell @ISQL_Script + @ISQL_Script1
How to solve this issue, Please help.
Thanks
Prashanth
@Prashant,
Is there a rule that i-sql commands have to be less than or equal to 4000 ??? I dont know…
I dont know answer to your question, but can you try one of this,
1. Increase length of the @ISQL_Script variable from 4000 to 8000 in declare statement.
2. If you are using SQL Server 2005, then use datatype varchar(max) for variable @ISQL_Script
Regards,
IM.
I just thought I would pass this along. I tried your example shown above without luck. The bat file can be run by double clicking. But calling it from the Query window gave me no love.
We got in touch with Microsoft and they told me to place the program folder location in double quotes. Like this:
master..xp_cmdshell “D:\backup\sqltest”
instead of single quote. Changing that made all the difference!
Best regards
I need to run an exe file from stored proc.
So, I wrote the stored proc like:
create proc callExe
As
EXEC xp_cmdshell ‘\\192.10.10.245\E:\PrintToPDFConsole.exe’
In this 192.10.10.245 is another system apart from the sql server.
and the exe file path is E:\PrintToPDFConsole.exe
But the exe file is not working when I executed the stored proc callExe.
like
Exec callExe
Please help in this issue.
Thank you.
Krushna
Hi Pinal,
I have made a Stored Procedure that imports the result of a SQL Query in an Excel Sheet everyday at 12am and saves it in the D Drive of a server using xp_cmdshell.
I now want to copy the file in another server so that everyone can access it.
I have written the following code…
create proc [dbo].[TEMP_report]
as
DECLARE @Command1 varchar(500)
DECLARE @Command2 varchar(500)
DECLARE @filename varchar(100)
DECLARE @date varchar(24)
select @date=convert(varchar(2),DATEPART(DD,getdate()))
+convert(varchar(2),DATEPART(MM,getdate()))
+convert(varchar(4),DATEPART(YYYY,getdate()))
SELECT @filename=’Report”‘+@date
select @Command1=’bcp “select * from tempdb..report” queryout D:\’+@filename+’.xls” -c -T’
Exec tempdb..xp_cmdshell @Command1
SELECT @filename=’Report’+@date
select @Command2=’COPY \\Server1\D$\’+@filename+’.xls \\Server2\D$\report’
Exec tempdb..xp_cmdshell @Command2
I am getting the following error in the last execute statement…
Access is denied.
0 file(s) copied.
NULL
I am able to copy the file with this command in DOS.
COPY \\Server1\D$\Report2832009.xls \\Server2\D$\report
Please guide me…
Hi Pinal,
Can I run an exe file using xp_cmdshell?
Please note that that exe file does not have an interface.
If yes, then please look at my stored proc :
create proc callExe
As
EXEC master.dbo.xp_cmdshell ‘C:\PrintToPDFConsole.exe’
The PrintToPDFConsole.exe file is in C drive of the server.
When I tried to execute this, I got the following output :
NULL
Unhandled Exception: System.ComponentModel.Win32Exception: No application is associated with the specified file for this operation
at PrintToPDF.Program.Main(String[] args)
NULL
That exe file is running fine when I double clicked it and also it is running from the command prompt.
I’m using sql server 2000.
Is there anything I need to change in the sql server ?
Please give the detail steps.
Thank you.
Krushna
Dear Pinal,
I have an SQL UPDATE query that checks particular for a condition and updates the table.
everytime i as an administrator manully executing this update query on SQL server. I want to create a windows schedule task that uses a batch file. I need this batch file to execute query and write the results into a log file.
In oracle it was very easy, Iam unaware of doing it in MS SQL 2005. please guide me.
Regards
@Syed Moiz
Even I have been trying to work on that task, to create a logfile.
For now I can say, creating log file it is possible when you run that script in a job.
When you create a step in a Job. if you go to advance, and check option Append Output file to existing file. This will create a log file on operating system.
Thanks,
Imran.
How to take the backup of sql server 2005 database with batch file? can you please help me it’s urgent.
HI,
I’m deleting folder using xp_cmdShell in a cursor,
the folders path is in a table
is there a way I can keep the outcome of the dos command
xp_cmdShell ‘RD d:\myfoldertoDelete\subfoldertoDelete /q/s’
the @results only gives me 0 or 2
I’d like to have the actual text if the folder was delted or missing.
Thanks
Neal
Hi Pinal,
I tried executing this simple command:
EXEC master.dbo.xp_cmdshell ‘C:\WINDOWS\system32\notepad.exe’
the query windows freezes without giving any output.
can i get windows shell script to get column vlue and total count of rows from sqlserver database.
Thanks,