ServerConnection.ExecuteNonQuery in SQLCMD Mode

Question

I am using the Microsoft Data-Tier Application framework to create a deployment script based on a DacPackage object. I am attempting to use the Microsoft.SqlServer.Management.Smo.Server class to execute this script...

SqlConnection deployConnection = new SqlConnection(connBuilder.ToString());
deployConnection.Open();
Server server = new Server(new ServerConnection(deployConnection));
server.ConnectionContext.ExecuteNonQuery(deployScript);

However, this errors out with...

Unhandled Exception: Microsoft.SqlServer.Management.Common.ExecutionFailureException:
  An exception occurred while executing a Transact-SQL statement or batch. --->
  System.Data.SqlClient.SqlException: Incorrect syntax near ':'.

I know that the answer to this problem is that I need to be in SQLCMD mode, but I don't know how to tell my ServerConnection to execute in said mode.

I guess my problem isn't as specific as what I state in the title. What I really need to be able to do is execute the script generated from the DacPackage via the .Net framework. Can anyone help me with this?

Answer 1

SQLCMD mode commands are not T-SQL commands; they only work in SQL Server Management Studio (SSMS) / Visual Studio (VS) and SQLCMD.EXE. SQLCMD-mode is inherently how SQLCMD.EXE works and can be manually enabled in SSMS / VS; it is a part of those applications and not something that can be done via a provider.

Those applications interpret the SQLCMD-mode commands and do not pass them through to SQL Server. SQLCMD-mode commands are parsed/executed first (which is how they are able to affect the SQL that is about to be submitted) and then the final version of the SQL is submitted to SQL Server.

Hence, the deployment SQL scripts generated by SQL Server Data Tools (SSDT) / Visual Studio need to be run via one of these three programs.

Since you have a .dacpac file already, Microsoft provides a few ways to publish those that you should check out:

• SqlPackage.exe and MSDeploy.exe. They are both described on the MSDN page for Project-Oriented Database Development using Command-Line Tools.
• DacServices.Deploy(). This can be done in C# via the DacServices Class.

You can also create a publish SQL script via DacServices.GenerateDeployScript(), but this won't change the situation as stated above since the publish / deploy SQL script, whether generated from Visual Studio "Publish {project_name}" or GenerateDeployScript(), is the same script. Meaning, it will have the SQLCMD-mode colon-commands such as :setvar and :on error exit as well as the SQLCMD-mode variables, which at the very least will be $(DatabaseName) which is used in the following line:

USE [$(DatabaseName)];

While it is possible to comment out the initial :setvar lines by setting the DacDeployOptions property of CommentOutSetVarDeclarations to true, that will still leave the :on error exit line as well as a line for :setvar __IsSqlCmdEnabled "True" that is used to detect whether or not SQLCMD-mode has been enabled. Just above this particular :setvar line is a comment stating:

/*
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; 
*/

So they really do intend that this script is only run via SQLCMD, whether through DOS -> SQLCMD.EXE or PowerShell -> Invoke-SqlCMD.

Technically, it is possible to generate a string of the deploy script contents (rather than to a stream) and manipulate that string by a) removing any colon-commands, and b) replacing "$(DatabaseName)" with whatever database you intend on deploying to. However, I have not tried this, I am not recommending this, and I am not sure it would work in all situations of what deployment scripts could be generated by SQL Server Data Tools. But it does seem like an option.

Also, minorly related: you don't need SMO to run SQL Scripts. SMO is means of interacting with SQL Server via objects rather than directly through T-SQL commands.

EDIT:
Links where others have tried this and found it did not work:

• http://www.sqlservercentral.com/Forums/Topic1161673-22-1.aspx
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d3f64b0-f6ed-44ad-99cc-ce4c8324c09e/running-sqlcmd-commands-using-smodatabaseexecutenonquery?forum=sqlsmoanddmo

Possibilities for getting the generated publish SQL script to work programmaticaly:

• C# via Process.Start to call SQLCMD.EXE -i filename.sql: http://msdn.microsoft.com/en-us/library/system.diagnostics.process.start(v=vs.110).aspx
• C# via open source library to handle "GO" statements and SQLCMD-mode colon-commands (currently handles SQL files, but can be easily updated to accept a string instead): https://github.com/rusanu/DbUtilSqlCmd
• PowerShell via Invoke-SqlCMD: http://www.sqlservercentral.com/Forums/Topic1502697-1351-1.aspx