ServerConnection.ExecuteNonQuery in SQLCMD Mode

c# sql-server sqlcmd data-tier-applications sql-server-data-tools
Dan Forbes picture Dan Forbes · Oct 28, 2014 · Viewed 8.3k times · Source

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

Solomon Rutzky picture Solomon Rutzky · Oct 28, 2014

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:

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:

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

Related questions

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More
Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database. This a button insert I make work well. private void btnAdd_Click(object …

Read More