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 (http://blog.SQLAuthority.com), BOL
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 Megha,
It may be possible that your SQL Server Service is running using account login which does not have access to your OS folder/file. Please check .
Regards,
Pinal
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,
hi, trying to find examples of how to export to excel with multiple heading columns before the actual data, but the first & second heading keeps being override by the third, the columns name, what i’m doing wrong?? please help..
DECLARE @beg_startdate datetime
DECLARE @end_enddate datetime
DECLARE @file_name varchar(200), @data_file varchar(200), @timestamp varchar(20), @columns varchar(2000)
DECLARE @sql varchar(2000),@headings varchar(200),@headings2 varchar(200)
BEGIN
SELECT @beg_startdate = DATEADD(wk, DATEDIFF(wk,-1,getdate()), -1)
SELECT @end_enddate = DATEADD(wk, DATEDIFF(wk,-1,getdate()), -1)
SET @timestamp = convert(varchar,@beg_startdate,10)
SELECT @headings=””’Destination Cost Report ””’+ ‘ as ‘+ ””’Destination Cost Report ””’
SELECT @headings2=@timestamp+ ‘ as ‘+ @timestamp
–+@timestamp+ CHAR(10)
SELECT @columns=coalesce(@columns+’,',”)+column_name+’ as ‘+column_name FROM INFORMATION_SCHEMA.COLUMNS where table_name=’th_Group_By_Dest’
SELECT @columns=”””+replace(replace(@columns,’ as ‘,””’ as ‘),’,',’,””’)
SET @file_name = ‘\\tcireports\Backup\Destination_’ + @timestamp + ‘.xls’
–Create a dummy file to have actual data
SELECT @data_file=substring(@file_name,1,len(@file_name)-charindex(‘\’,reverse(@file_name)))+’\desttest.xls’
– Generate column names in the passed EXCEL file
– -c Output in ASCII with the default field terminator (tab) and row terminator (crlf)
SET @sql=’EXEC master..xp_cmdshell ” bcp “SELECT * FROM (SELECT ‘+@headings+’) as t” queryout “‘+ @file_name+ ‘” -c”’
EXEC (@sql)
SET @sql=’EXEC master..xp_cmdshell ” bcp “SELECT * FROM (SELECT ‘+@headings2+’) as t” queryout “‘+ @file_name+ ‘” -c”’
EXEC (@sql)
SET @sql=’EXEC master..xp_cmdshell ” bcp “SELECT * FROM (SELECT ‘+@columns+’) as t” queryout “‘+ @file_name+ ‘” -c”’
EXEC (@sql)
– –Generate data in the dummy file
SET @sql=’EXEC master..xp_cmdshell ” bcp “SELECT * FROM TCIReports.MarginReport.dbo.th_Group_By_Dest ” queryout “‘+ @data_file + ‘” -c”’
EXEC (@sql)
–
– – –Copy dummy file to passed EXCEL file
SET @sql=’EXEC master..xp_cmdshell ” type ‘+ @data_file+’ >> ‘+ @file_name + ””
EXEC (@sql)
END
Hi Pinal,
I have a batch file. This calls a console application (say like notepad) which executes for a while & exits normally. No user intervention is required.
When i call the batch file using xp_cmdshell. The command seems to execute forever but the console application never starts.
I run the batch file by remote desktoping to the machine and the file is available in the appropriate location.
Any thoughts on this would be useful.
Thanks
Suga
Did you observe the file is running in the background process, I couldn’t able to see. Do you have any idea of how can I run this and see the file on screen.
Thanks in Advance.
Hello Venkat,
From SQL Server we can not open an application with user interface. Even the process switch to application but without interface it does not get responce from application and stay in waiting. After all the process has to be killed.
Usnig t-sql we can perform only tasks that do not require user interaction using xp_cmdshell, OLE automation extended procedures or by CLR coding. On the first glance it seems a limitation but this is not the target SQL server is designed for.
If you explain your requirement then the complete approach to acheive that can be suggested.
Dear Pinal,
I have a batch file that runs T-SQL query, How can we catch exception/error thrown while running sql query in a batch file?
Please help me. It is very critical now. Thanks in Advance.
Hi Ahmed,
One solution is to send an email from catch block, so you can come to know that there is an error in query.
Thanks,
Tejas
i am fresser student & i want to know about sql job
and how to prepared for job as will as interview for as database administretore
please help me
and i have also quarry
I have a batch file that runs T-SQL query, How can we catch exception/error thrown while running sql query in a batch file?
i wait thank………………………..
Dear buddies,
I have around 200 bsps to be run from command prompt so I created it as a bat file but its not working.
Any suggestion? I added a semicolon at the end of each bcp command.
Eg:
bcp “SELECT * FROM dbname.schema.tablename” queryout C:\tablename.txt -c -U login -P password -S servername;
bcp “SELECT * FROM dbname.schema.tablename” queryout C:\tablename.txt -c -U login -P password -S servername;
each with different table names and file names
Please advice.
Nith
Hi Pinal,
I have an macro in access that I want to execute from a SQL Server trigger using xp_cmdshell. I am able to run other batch files from xp_cmdshell (runs under ntauthority\system account), but not this one. It shows executing query and kind of hangs..The batch file just creates a csv file for me. How can I execute it from SQL Server?? Is there a way I can invoke a Windows task (disabled) from SQL server trigger???
PLease suggest
Thanks
Neha
Hi Pinal,
I developed a job, with the next sentense into them:
exec master..xp_cmdshell ‘del c:\test\*.bak’
and recived the next error:
Executed as user: PUENTEDBAMAIN\SYSTEM. The process could not be created for step 1 of job 0x33CD203A90451545B466D4CCAA10428C (reason: The system cannot find the file specified). The step failed.
I have SQL Server 2000, with the last SP.
The job runs with the sa user.
Thanks
Marcelo
Hi Pinal,
First of Nice Article, Learn lot from it.
Question:
Is there a way we can supply UserName and PWD when we use xp_cmdshell with COPY command?
Please let me know your thoughts
Thanks
Keyun
Hi Pinal
I am using stored procedure (cmd_shell) to run an .exe file but it is just keep executing. please tell me how can i run an exe file from SQL Server 2005.
Hi,
Thank you, I have setup a job to run batch file. works very well.
Dear Pinal,
I want to run a batch file
exec master..xp_cmdshell ‘c:\psftp.bat’
containing secured FTP command
psftp 172.16.173.226 -l sftpuser -pw 12345 -b cmdFile.txt
but it gives me the following error
C:\>psftp 172.16.173.226 -l sftpuser -pw 12345 -b cmdFile.txt
Fatal: Network error: Software caused connection abort
NULL
when i am just double clicking the batch file it works fine
Please tell me the procedure to run the same.
Regards
any equivalent command in MYSQL ?
You need ask this question in mysql forums
ALTER PROCEDURE [dbo].[ProcessXMLCSVTest1]
as
declare @filename varchar(255)
BEGIN
INSERT INTO [Test].[dbo].[XMLCSVTable1]
([ID]
,[RollNo]
,[SchoolNo]
,[SchoolAdd])
SELECT convert (varchar(10),a.[ID] ,101),convert (varchar(10),a.[RollNo] ,101),
convert (varchar(10),a.[SchoolNo] ,101),convert (varchar(10),a.[SchoolAdd] ,101)
FROM OPENROWSET( BULK ‘C:\Desktop\Chandresh\CSVInput.txt’ ,
FORMATFILE = ‘C:\Chandresh\CSVXML.txt’) AS a;
end
This is my Store Proc. i created Variable @FileName for C:\Desktop\Chandresh\CSVInput.txt’ this path.
My Text File Path is Dynamic .so can anyone help me what should i do for mY Dynamic Text File path.
i will be appriciate for comment.
Thanks!!!!
Hi Dave,
I have an issue, where my storedprocedure calling a batch file and it consist of application exe to trigger email notification.
I am using an administrator account both in OS and SQL level and executing the SP.
my sp is like this..
CREATE Procedure [dbo].[spemailpassword_exam]
@empno int
As
Declare @icount int
–DECLARE @ssql varchar(255)
BEGIN
INSERT INTO dbo.CanPay_Email_Info(EmpNo,EmailTemplate)
Values(@empno,8)
SET @icount=0
SELECT @icount=@icount + 1
END
IF @icount > 0
BEGIN
EXEC master.dbo.xp_cmdshell ‘D:\jeevan\OTTest\target\jeevan.bat’
–SELECT @ssql = ‘exec master.dbo.xp_cmdshell ‘D:\jeevan\OTTest\target\jeevan.HRIT.CanPayroll.EmailNotifications”
–EXEC (@ssql)
END
Hi Pinal,
I have problem adding a SQL job with the below command:-
exec master.dbo.xp_cmdshell ‘”C:\Program Files\WinZip\winzip32.exe” /autorunjobfile S:\POSTOFF\_SCR\extract_encryption.wjf’
The job can be started but it is like goin into a loop and will not complete.
Can you help me on this?
plz help me I m in trouble..I hv made project in VS 2010 DB sqlserver 2008 windows form and make EXE file.I hv install Exe file but I think there is some DB connection error.plz tell me what should I need and what should I Do to run this Exe properly….
reply me as soon as posible…
hi i am using in my php project as a database sqlserver when i run the page i got this below error
this is error
Unable to coonect to server :TOMEDES\SQLEXPRESS IN D:\htdocs\pmo\getattandance.php line 18 couldn’t connect to sql server on TOMEDES\SQLEXPREESS
my php code is
<?php
require_once('include/include.php');
set_time_limit(0);
$lastdate = getlastdate();
for($inte=0;$inte= strtotime($curdate))
$edate = $curdate;
//echo $sdate.”===”.$edate; die;
if(strtotime($sdate) >= strtotime($edate))
die;
//$sdate = ‘Aug 16 2011 12:00AM’;
//$edate = ‘Aug 17 2011 12:00AM’;
$query = “SELECT * FROM $table_name where AttendanceDate > ‘”.$sdate.”‘ and AttendanceDate queryToArray($sql);
}
}
function getlastdate()
{
global $db;
$sql=”SELECT AttendanceDate FROM attendancelog order by id desc limit 1″;
$list=$db->queryToArray($sql);
return $list;
}
?>
This post couldnt be more precise!
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