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