none
Batch file syntax for sqlcmd

    Question

  • Hi All: in server 2008 R2, I have a simple sqlcmd to back up my sqlexpress dBs that runs properly from the command line:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" /S .\SQLEXPRESS /E /Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'"

    I'm trying to convert this to a batch script so that I can run it using task scheduler, but it seems like I need to escape certain characters or I get """ is not recognized as an internal or external command..."

    I tried escaping the quote marks, like this:

    ^"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd^" /S .\SQLEXPRESS /E /Q ^"EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'^"

    but when I test running the batch, cmd.exe just consumes 100% of CPU and doesn't seem to execute the actual commands. Is there any way to debug the batch file so I know what the proper syntax should be?

    Sunday, April 29, 2012 3:56 PM

Answers

  • It seems there is a special character in the commands in the batch file.

    Could you retype (not copy/paste) the commands in a new batch file? You can forget about using an escape character for a single or double quote. Quotes work just fine in my testing.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | (http://aalamrangi.wordpress.com)


    Friday, May 04, 2012 5:00 PM

All replies

  • Is there any way to debug the batch file so I know what the proper syntax should be?

    Hello,

    You can use SSMS (SQL Server Management Studio) for this, in menu "Query" you can switch to SQLCMD mode to test your script.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Sunday, April 29, 2012 4:26 PM
  • Thanks Olaf, but I already know the sql command works properly...I can execute it from the command line without a problem.

    The problem is converting it to a batch file so I can schedule it to run using Task Scheduler. I can't get the batch file to run without compile errors, and task scheduler's error messages aren't informative.

    Sunday, April 29, 2012 8:48 PM
  • Does the folder named 'c:\tempStuff\' exist ?

    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Monday, April 30, 2012 12:38 AM
    Moderator
  • Yes...I can run this sql command from a command prompt without a problem. It's only when I try to run it as a batch file that I get the error.

    In other words: this works properly from the command line:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" /S .\SQLEXPRESS /E /Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F"

    but if I try to save this command as a batch file, and run the batch file, like:

    c:\tempStuff\sqlbackup.bat

    I receive errors like  """ is not recognized as an internal or external command..."

    So I tried escaping the quote marks with an escape character (see my first post in this thread), but I'm still getting errors parsing the batch file. 

    Tuesday, May 01, 2012 3:10 PM
  • Go through the given link, may be its helpful.

    http://geekepisodes.com/sqlbi/2011/executing-the-script-in-batch-file-using-sqlcmd-command/

    Friday, May 04, 2012 11:35 AM
  • Hmmm...I feel like I've tried that already, but I went back and verified that my sqlcmd executed properly from the command line:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" -E -S ".\SQLEXPRESS" -Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'"

    I then saved it as a BAT file, and if I try to run the BAT from the command line, I get the following error:

    c:\tempStuff>sqlbackupcore.bat

    c:\tempStuff>■"
    '■"' is not recognized as an internal or external command,
    operable program or batch file.

    This is the same error I've been struggling with all along.

    Friday, May 04, 2012 2:24 PM
  • Hmmm...I feel like I've tried that already, but I went back and verified that my sqlcmd executed properly from the command line:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd" -E -S ".\SQLEXPRESS" -Q "EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'"

    I then saved it as a BAT file, and if I try to run the BAT from the command line, I get the following error:

    some characters do have special semantics in command shell.

    Why do you not write the T-SQL statement into a file and just pass the file to sqlcmd using -i option?

    the file (test.ql) containing the T-SQL statement would look like:

    EXEC sp_BackupDatabases @backupLocation='c:\tempStuff\', @backupType='F'

    and your batch file would look like:

    C:\"Program Files\Microsoft SQL Server"\100\Tools\Binn\sqlcmd.exe -E -S ".\SQLEXPRESS" -i test.sql -n

    you can generate the file test.sql from within the batch file before the call of sqlcmd.exe or just create it hardcode. you've probably to specify the location of test.sql in the batch file.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.




    Friday, May 04, 2012 2:37 PM
  • Hi Daniel: Thanks for your help. I followed your suggestion and created a separate sql script, and then a new BAT file called "sqlbackupNew.bat" 

    (BTW: I'm using Notepad to create/edit the files)

    The BAT file has a single command in it:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -E -S ".\SQLEXPRESS" -i sqlbackup.sql -n

    And if I try to run the BAT from the command line, I still am getting errors:

    c:\tempStuff>sqlbackupNew.bat

    c:\tempStuff>■"
    '■"' is not recognized as an internal or external command,
    operable program or batch file.

    Friday, May 04, 2012 3:27 PM
  • please put at the beginning of the batch file the following command:

    echo on

    you will then see if sqlcmd is executed before the error or not.

    could you try to start the batch file with complete batch: c:\tempStuff\sqlbackupnew.bat

    and please put the double quote around sqlcmd.exe as show in my sample.

    To check proper setup of your batch environment, create an batch file with "echo" as the only line (without double quote) and try to execute it.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.




    Friday, May 04, 2012 3:35 PM
  • Could you put a screenshot of this error message?

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | (http://aalamrangi.wordpress.com)

    Friday, May 04, 2012 4:24 PM
  • Could you put a screenshot of this error message?

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | (http://aalamrangi.wordpress.com)


    Friday, May 04, 2012 4:31 PM
  • please put at the beginning of the batch file the following command:

    echo on

    you will then see if sqlcmd is executed before the error or not.

    could you try to start the batch file with complete batch: c:\tempStuff\sqlbackupnew.bat

    and please put the double quote around sqlcmd.exe as show in my sample.

    To check proper setup of your batch environment, create an batch file with "echo" as the only line (without double quote) and try to execute it.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.




    OK...I amended the script as you suggested...I didn't notice how you had quoted only the part of the command line with spaces in it...so my BAT file now looks like this:

    echo on
    C:\"Program Files\Microsoft SQL Server"\100\Tools\Binn\sqlcmd.exe -E -S ".\SQLEXPRESS" -i c:\tempstuff\sqlbackup.sql -n

    That results in a similar error:

    c:\tempStuff>sqlbackupNew.bat
    
    c:\tempStuff>■e
    '■e' is not recognized as an internal or external command,
    operable program or batch file.
    I also tried executing the BAT using the full path, but I get the same error.


    • Edited by jmbarnes Friday, May 04, 2012 4:40 PM
    Friday, May 04, 2012 4:39 PM
  • It seems there is a special character in the commands in the batch file.

    Could you retype (not copy/paste) the commands in a new batch file? You can forget about using an escape character for a single or double quote. Quotes work just fine in my testing.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | (http://aalamrangi.wordpress.com)


    Friday, May 04, 2012 5:00 PM
  • It seems there is a special character in the commands in the batch file.

    Could you retype (not copy/paste) the commands in a new batch file? You can forget about using an escape character for a single or double quote. Quotes work just fine in my testing.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | (http://aalamrangi.wordpress.com)


    aaargh...it seems like pasting in the line was the problem all along. :(

    After retyping the command string manually, I finally got it to work. Thanks for following through on this.

    Friday, May 04, 2012 5:22 PM
  • The squares in the screenshot gave it away. Squares (or gibberish characters) like that in a command prompt window indicate a special character e.g. tab, new line, carriage return etc.

    Although I could see that in the error output that you've been pasting in this thread but I just wanted to be sure that they were actualy coming from the error output and not just while pasting that text here. So asked for a screenshot.

    Thanks for marking the reply as answer! Have a great day!


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | (http://aalamrangi.wordpress.com)

    Friday, May 04, 2012 6:36 PM
  • Aalam,

    Massive thanks! Been going round in circles on this - I cannot believe that all I had to do was retype the command rather than paste!

    Friday, August 10, 2012 11:11 PM
  • Met the same issue. The problem was that I saved bat file as UNICODE. When I resaved this file as ANSI - command started to work fine
    • Edited by dmisav Friday, August 31, 2012 10:45 AM
    Friday, August 31, 2012 10:44 AM