Executing Publish Scripts with SQLCMD doesn't return

Question

I am having an issue running SQL scripts generated by the Publish > Generate Scripts dialog on a SQL Server Database Project (Visual Studio 2013).

The generated script executes fine and usually with 2 - 10 seconds if run from the SQL Management Studio in SQLCMD mode. However, if run using SQLCMD.exe (via CMD or Batch File) it seems to hang and never finish. I am guessing that there are some additional parameters (preset?) that the SQL Management Studio is using to execute the script but I have been unable to get this to work with SQLCMD directly. The SQL script that is being generated is usually only about 200 lines max. Here is the command I am using (which is being run from a batch file, with Run as Administrator, on Windows Server 2012R2):

"D:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" -S SQLSERVER1 -i "D:\Database\MyDatabase.publish.sql"

I have not seen any errors but the SQLCMD just never exits, prompting me to have to kill the process. Below is an example of the type of script created by the Publish dialog:

/*
Deployment script for MyDatabase

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "MyDatabase"
:setvar DefaultFilePrefix "MyDatabase"
:setvar DefaultDataPath "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET PAGE_VERIFY NONE 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)];


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
PRINT N'Dropping FK_ORDR_BPO_ORDR_MASTER...';


GO
ALTER TABLE [dbo].[ORDR_BPO] DROP CONSTRAINT [FK_ORDR_BPO_ORDR_MASTER];


GO
PRINT N'Dropping FK_ORDR_CommonData_ORDR_MASTER...';


GO
ALTER TABLE [dbo].[ORDR_CommonData] DROP CONSTRAINT [FK_ORDR_CommonData_ORDR_MASTER];


GO
PRINT N'Dropping FK_ORDR_BPO_ProductType_ValuationProductCategory...';


GO
ALTER TABLE [dbo].[ORDR_BPO_ProductType] DROP CONSTRAINT [FK_ORDR_BPO_ProductType_ValuationProductCategory];


GO
PRINT N'Creating [dbo].[LOG_ServiceAction]...';


GO
CREATE TABLE [dbo].[LOG_ServiceAction] (
    [LOG_ServiceActionID] INT            IDENTITY (1, 1) NOT NULL,
    [ServiceActionName]   VARCHAR (255)  NOT NULL,
    [User_ID]             INT            NOT NULL,
    [TransactionID]       VARCHAR (32)   NOT NULL,
    [Message]             VARCHAR (1000) NOT NULL,
    [ActionDate]          DATETIME       NOT NULL,
    [FilePath]            VARCHAR (2000) NULL,
    [ExpiresDateUTC]      DATETIME       NOT NULL,
    CONSTRAINT [PK_LOG_ServiceAction] PRIMARY KEY CLUSTERED ([LOG_ServiceActionID] ASC)
);


GO
PRINT N'Creating FK_ORDR_BPO_ORDR_MASTER...';


GO
ALTER TABLE [dbo].[ORDR_BPO] WITH NOCHECK
    ADD CONSTRAINT [FK_ORDR_BPO_ORDR_MASTER] FOREIGN KEY ([MasterOrder_ID]) REFERENCES [dbo].[ORDR_MASTER] ([MasterOrder_ID]);


GO
PRINT N'Creating FK_ORDR_CommonData_ORDR_MASTER...';


GO
ALTER TABLE [dbo].[ORDR_CommonData] WITH NOCHECK
    ADD CONSTRAINT [FK_ORDR_CommonData_ORDR_MASTER] FOREIGN KEY ([MasterOrder_ID]) REFERENCES [dbo].[ORDR_MASTER] ([MasterOrder_ID]);


GO
PRINT N'Checking existing data against newly created constraints';


GO
USE [$(DatabaseName)];


GO
ALTER TABLE [dbo].[ORDR_BPO] WITH CHECK CHECK CONSTRAINT [FK_ORDR_BPO_ORDR_MASTER];

ALTER TABLE [dbo].[ORDR_CommonData] WITH CHECK CHECK CONSTRAINT [FK_ORDR_CommonData_ORDR_MASTER];


GO
PRINT N'Update complete.';


GO

Any help on this would be appreciated as this is the last MANUAL step in automating a deployment process.

UPDATE: So I have some more information on this that may help someone who knows in diagnosing this issue... What I've come across is that the Publish script is setting variables at the beginning such as DatabaseName (refer to example above). When running in SCLCMD mode from the MS SQL Management Studio, the script will sometimes never return. If, however, I run the script NOT in SQLCMD mode, let it fail, and then re-run it in SQLCMD mode, it executes successfully.

There is a bit of code that I notice returns an error (unfortunately, I do not have the error message but I will update when I get the error again) when the script fails to return whilerunning in SQLCMD mode:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END

The script is failing on the ALTER DATABASE [$(DatabaseName)] command. I suspect that this is the same scenario being played out when running from the CMD line using SQLCMD.exe. I hope this helps but I will continue to find a solution and EDIT this post when I do. Thanks.

Answer 1

Well.. after looking into this for some time I have finally figured out the resolution to my problem (if not the answer to why it doesn't work).

There is a option during publishing or generation of a database script from the database project that was stopping the deployment. As mentioned in the original question, the ALTER DATABASE [$(DatabaseName)] was the culprit.

If you are in your Publish Database dialog, you can click the "Advanced..." button to see additional publishing options. Un-checking the "Deploy database properties" checkbox will create your publish script WITHOUT the initial ALTER DATABASE statements which SQLCMD keeps hanging on.

Hopefully this helps save someone else some time. Cheers!