locked
Execute a ,bat file RRS feed

  • Question

  • Hello,

    I have a .bat file on the D: drive. The executable needs a paramter to be entered before it can be run. How can I exec this file from sqlcmd. I am using SQL Server 2008. Also the file is on the same server as the database is on.

    Thanks,
    KK

    Thursday, June 3, 2010 7:43 PM

Answers

  • Hello,

    To run a system command from SQLCMD add “!!” at the beginning of the command. For more information see the following link:

    http://blog.sqlauthority.com/2009/01/05/sql-server-sqlcmd-vs-osql-basic-comparison/

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Tom Li - MSFT Tuesday, June 8, 2010 3:07 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 9, 2010 8:43 AM
    Friday, June 4, 2010 12:24 AM
  • Hello,

    I have a .bat file on the D: drive. The executable needs a paramter to be entered before it can be run. How can I exec this file from sqlcmd. I am using SQL Server 2008. Also the file is on the same server as the database is on.

    Thanks,
    KK


    Use XP_CmdShell instead

    First of all enable XP_CMDShell

    EXEC sp_configure ‘show advanced options’, 1
    GO
    
    —- To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    
    —- To enable the feature.
    EXEC sp_configure ‘xp_cmdshell’, 1
    GO
    
    —- To update the currently configured value for this feature.
    RECONFIGURE
    GO 

    after that

    EXEC master..xp_CMDShell 'c:\batchfile.bat'  

     

    if you want to pass params to your batch file

    DECLARE @Variable VARCHAR(100)
    DECLARE @CMDSQL VARCHAR(1000)
    SET @Variable = 'MyVariable'
    SET @CMDSQL = 'c:\batchFile.bat' + @Variable
    EXEC master..xp_CMDShell @CMDSQL

     

     

    • Proposed as answer by Tom Li - MSFT Tuesday, June 8, 2010 3:15 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 9, 2010 8:43 AM
    Friday, June 4, 2010 4:23 PM
  • hey check this link to call .bat file from SQL Server

    How to call a bat file from SQL Server

    http://insqlserver.com/post/2010/06/05/How-to-call-a-bat-file-from-SQL-Server.aspx


    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin
    • Marked as answer by Tom Li - MSFT Wednesday, June 9, 2010 8:43 AM
    Saturday, June 5, 2010 8:42 PM

All replies

  • Hello,

    To run a system command from SQLCMD add “!!” at the beginning of the command. For more information see the following link:

    http://blog.sqlauthority.com/2009/01/05/sql-server-sqlcmd-vs-osql-basic-comparison/

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Tom Li - MSFT Tuesday, June 8, 2010 3:07 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 9, 2010 8:43 AM
    Friday, June 4, 2010 12:24 AM
  • So will the following be a correct syntax on SQLCMD:

    >1 !! D:/SQL/BATScript.bat

    ?

    Thanks,
    KK

    Friday, June 4, 2010 3:34 PM
  • Hello,

    I have a .bat file on the D: drive. The executable needs a paramter to be entered before it can be run. How can I exec this file from sqlcmd. I am using SQL Server 2008. Also the file is on the same server as the database is on.

    Thanks,
    KK


    Use XP_CmdShell instead

    First of all enable XP_CMDShell

    EXEC sp_configure ‘show advanced options’, 1
    GO
    
    —- To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    
    —- To enable the feature.
    EXEC sp_configure ‘xp_cmdshell’, 1
    GO
    
    —- To update the currently configured value for this feature.
    RECONFIGURE
    GO 

    after that

    EXEC master..xp_CMDShell 'c:\batchfile.bat'  

     

    if you want to pass params to your batch file

    DECLARE @Variable VARCHAR(100)
    DECLARE @CMDSQL VARCHAR(1000)
    SET @Variable = 'MyVariable'
    SET @CMDSQL = 'c:\batchFile.bat' + @Variable
    EXEC master..xp_CMDShell @CMDSQL

     

     

    • Proposed as answer by Tom Li - MSFT Tuesday, June 8, 2010 3:15 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 9, 2010 8:43 AM
    Friday, June 4, 2010 4:23 PM
  • hey check this link to call .bat file from SQL Server

    How to call a bat file from SQL Server

    http://insqlserver.com/post/2010/06/05/How-to-call-a-bat-file-from-SQL-Server.aspx


    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin
    • Marked as answer by Tom Li - MSFT Wednesday, June 9, 2010 8:43 AM
    Saturday, June 5, 2010 8:42 PM