SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

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

About these ads

53 thoughts on “SQL SERVER – Running Batch File Using T-SQL – xp_cmdshell bat file

  1. 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

  2. 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

  3. 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.

  4. 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”

  5. Sorry the error msg is shown as

    “‘test.bat’ is not recognized as an internal or external command,….operable program or batch file….NULL”

  6. 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.

  7. 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

  8. 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

  9. @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.

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. @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.

  16. 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

  17. 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.

  18. 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

  19. 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

  20. 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.

  21. 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.

  22. 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.

  23. 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………………………..

  24. 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

  25. 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

  26. 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

  27. 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.

  28. 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

  29. 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!!!!

  30. 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

  31. 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?

  32. 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…

  33. 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;

    }

    ?>

  34. 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.

  35. 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

  36. 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

  37. 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.

  38. xp_cmdshell ‘dtexec.exe /F “C:\Cube\ResRentals.dtsx”‘………runns forever and never stops…..Can anyone help me out here please!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s