All geek questions in one place

ServerConnection.ExecuteNonQuery in SQLCMD mode

I am using the Microsoft Data-Tier Application framework to create a DacPackage- based script deployment. I am trying 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, these errors are 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 ServerConnection in the specified mode.

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

+6
source share
1 answer

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 essentially how SQLCMD.EXE works and can be manually enabled in SSMS / VS; this is part of these applications, not something that can be done through the provider.

These applications interpret SQLCMD mode commands and do not pass them to SQL Server. SQLCMD-mode commands are first parsed / executed (this is how they can affect the SQL that should be sent), and then the final version of SQL is sent to SQL Server.

Therefore, SQL deployment scripts generated by SQL Server Data Tools (SSDT) ​​/ Visual Studio must be run through one of these three programs.

Since you already have a .dacpac file, Microsoft offers several ways to publish the ones you should check:

You can also create an SQL publication publication via DacServices.GenerateDeployScript () , but this will not change the situation as indicated above since the publication / deployment of the SQL script, regardless of whether it is generated from Visual Studio "Publish {project_name}" or GenerateDeployScript() is the same script. Meaning, it will have SQLCMD-mode colon commands, such as :setvar and :on error exit , as well as SQLCMD mode variables, which at least will be $(DatabaseName) , which are used in the following line:

 USE [$(DatabaseName)];

Although you can comment on the source lines :setvar by setting the DacDeployOptions CommentOutSetVarDeclarations property to true , which will still leave the line :on error exit , as well as the line for :setvar __IsSqlCmdEnabled "True" , which is used to determine if SQLCMD mode has been enabled. Just above this line :setvar has a comment that says:

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

Therefore, they really intend that this script only runs through SQLCMD, whether through DOS → SQLCMD.EXE or PowerShell → Invoke-SqlCMD.

Technically, you can create a script content line for deployment (and not in stream ) and manage this line: a) removing any colon commands and b) replacing "$ (DatabaseName)" with any database that you are going to use for deployment. However, I have not tried this, I do not recommend it, and I am not sure that it will work in all situations, which deployment scenarios can be created using SQL Server Data Tools. But this seems like an option.

It is also slightly related: you do not need SMO to run SQL scripts. SMO is a means of interacting with SQL Server through objects, and not directly through T-SQL commands.

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

Features to get the generated SQL script publication to work programmatically:

+9
source

Source: https://habr.com/ru/post/977375/

More articles:


All Articles