title: Executing Publish Scripts with SQLCMD doesn't return
body: <p>I am having an issue running SQL scripts generated by the Publish > Generate Scripts dialog on a SQL Server Database Project (Visual Studio 2013).</p>
  
  <p>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):</p>
  
  <pre><code>"D:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" -S SQLSERVER1 -i "D:\Database\MyDatabase.publish.sql"
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>/*
  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
  </code></pre>
  
  <p>Any help on this would be appreciated as this is the last MANUAL step in automating a deployment process.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET READ_COMMITTED_SNAPSHOT OFF;
      END
  </code></pre>
  
  <p>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.</p>