4

Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt. They simply check the mirroring status, suspend mirroring and then check the status again. (To minimize dependencies and ease migration from one server to another, I want to keep all commands in a single file.)

select cast(DB_NAME(database_id) as char(30)),
        cast(mirroring_state_desc as char(15))
from sys.database_mirroring
where mirroring_state_desc is not null;
go;

ALTER DATABASE db1 SET PARTNER SUSPEND;
ALTER DATABASE db2 SET PARTNER SUSPEND;
go;

select cast(DB_NAME(database_id) as char(30)),
        cast(mirroring_state_desc as char(15))
from sys.database_mirroring
where mirroring_state_desc is not null;
go;

So, I wrote this batch file:

SETLOCAL ENABLEDELAYEDEXPANSION
SET @SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
go ^
^
ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
go ^
^
select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
go
::
echo.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%@SQLSTRING%"

However, it fails with syntax errors at the "go" commands. I've tried "go" and "go;" with no luck.

Any tips or guidance to make this work would be appreciated.

  • Why not use a separate sql file and reference it with the -i parameter for sqlcmd? Another option is PowerShell: have you considered it? – spaghettidba Apr 22 '15 at 9:33
  • Regarding a separate file: I mentioned that I want to keep everything in one file.. regarding PowerShell: no priivs to do so. For some reason, throughout the division, Windows sysadmins are touchy about allowing anyone to use PS. – RonJohn Apr 22 '15 at 15:45
  • GO is a batch seperator - NOT a T-SQL command as opposed to ; which will end TSQL statement (statement terminator). Also, ; is a part of ANSI SQL-92 standard. – Kin Shah Apr 22 '15 at 21:29
3

There are a couple of problems with this approach:

  1. The immediate problem is that you cannot continue an empty line. So the two lines (following each go ^ line) that are only the single character ^ are causing the line to end and not continue. It has nothing to do with the go. So you can just add a space before the carrot and it will build the full string.

  2. However, even if you fix problem #1, you are still left with invalid SQL since GO needs to be on a line by itself or, at most, optionally followed by some whitespace and an INT value for how many times to repeat the prior batch (e.g. GO 12). But, using line continuation via ^ there won't be any embedded returns so everything will be submitted as a single line, and that is not valid syntax with the GO commands in there.

This leaves you with two options:

  1. If you are submitting SQL that does not require any batch separation, then just get rid of the GO commands (and wouldn't hurt to get rid of the @ sign, and the ENABLEDELAYEDEXPANSION and final GO aren't needed either):

    SET SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
            cast(mirroring_state_desc as char(15)) ^
    from sys.database_mirroring ^
    where mirroring_state_desc is not null; ^
     ^
    ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
    ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
     ^
    select cast(DB_NAME(database_id) as char(30)), ^
            cast(mirroring_state_desc as char(15)) ^
    from sys.database_mirroring ^
    where mirroring_state_desc is not null;
    
    ECHO.
    sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%SQLSTRING%"
    
  2. If you do need batch separation (e.g. you have a CREATE PROCEDURE statement or something like that), then you can create a SQL file in your CMD script, add each line individually, and then use that as the SQL script that SQLCMD will run. This allows you to embed returns as well as use GO commands:

    @ECHO OFF
    
    ECHO. > MultiLine.sql
    ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
    ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
    ECHO from sys.database_mirroring >> MultiLine.sql
    ECHO where mirroring_state_desc is not null; >> MultiLine.sql
    ECHO GO >> MultiLine.sql
    ECHO. >> MultiLine.sql
    ECHO ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; >> MultiLine.sql
    ECHO ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; >> MultiLine.sql
    ECHO GO >> MultiLine.sql
    ECHO. >> MultiLine.sql
    ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
    ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
    ECHO from sys.database_mirroring >> MultiLine.sql
    ECHO where mirroring_state_desc is not null; >> MultiLine.sql
    
    ECHO.
    sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -i MultiLine.sql
    
  • 1
    Option 1 worked like a charm. – RonJohn Apr 23 '15 at 3:49
3

Get rid of GO altogether. It's recognized by sqlcmd only in the interactive prompt or when reading from a file using the -i switch. If some statements need to be in a separate batch, use EXEC to wrap them. Example: EXEC('CREATE VIEW SomeView')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.