ServerConnection.ExecuteNonQuery在SQLCMD模式 [英] ServerConnection.ExecuteNonQuery in SQLCMD Mode

问题描述

我使用的是微软数据层应用程序框架来创建基于 DacPackage部署脚本对象。我试图使用 Microsoft.SqlServer .Management.Smo.Server 类来执行这个脚本...

  SqlConnection的deployConnection =新的SqlConnection（connBuilder。的ToString（））; 
 deployConnection.Open（）; 
 Server服务器=新服务器（新ServerConnection（deployConnection））; 
 server.ConnectionContext.ExecuteNonQuery（deployScript）; 
  

然而，这样的错误了......用

 未处理的异常：执行Transact-SQL语句或批处理时出现
例外：Microsoft.SqlServer.Management.Common.ExecutionFailureException。 ---> 
 System.Data.SqlClient.SqlException：附近有语法错误：。 
  

我知道这个问题的答案的问题是，我需要在SQLCMD模式，但我不知道如何告诉我的 ServerConnection 执行中所述模式。

我想我的问题是不是越具体是什么我在标题中注明。我真正需要的是能够做的就是执行通过.net框架从DacPackage生成的脚本。谁能帮我这个

解决方案

SQLCMD模式命令的不的T-SQL命令？;他们只在SQL Server Management Studio中（SSMS）/ Visual Studio中（VS）和SQLCMD.EXE工作。 SQLCMD模式本质上是SQLCMD.EXE是如何工作的，可以在SSMS / VS手动启用;它是这些应用程序，而不是东西，可以通过提供商来完成的一部分。

这些应用解释SQLCMD模式命令，不通过它们传递到SQL Server 。 SQLCMD模式命令解析/执行第一（这是他们如何能够影响SQL是即将提交），然后SQL的最终版本被提交到SQL Server。

因此，通过SQL Server数据工具生成的部署SQL脚本（SSDT）/ Visual Studio的需要可以通过这三个方案之一运行。

既然你已经在 .dacpac 文件中，微软提供了一些方法来发布那些你应该检查出：

• SqlPackage.exe 和 MSDeploy.exe 。他们都在MSDN页面上使用命令 - 面向项目的数据库开发描述行工具的。


• DacServices.Deploy（）。这可以在C＃中通过 DacServices类来完成。

您还可以通过创建的 DacServices.GenerateDeployScript（），但是这不会改变如上自发布/部署SQL脚本指出，无论是从Visual Studio生成的情况发布{ PROJECT_NAME}或 GenerateDeployScript（），是相同的脚本。这意味着，将有SQLCMD模式冒号命令，如：SETVAR 和：出错退出以及在SQLCMD模式变量，这至少是将 $这是在下面一行用（数据库名）：

 使用[$（数据库名）]; 
  

虽然可以注释掉初始：SETVAR 通过设置 DacDeployOptions 的CommentOutSetVarDeclarations 以真正 ，仍然将离开：出错退出行以及为 A线：SETVAR __IsSqlCmdEnabled真的用于检测SQLCMD模式是否已启用。略高于这个特殊的：SETVAR 行是注释说明：

<预类=郎-SQL prettyprint-覆盖> / *
检测SQLCMD模式和禁用脚本的执行如果不支持SQLCMD模式。启用S​​QLCMD模式后，
。要重新启用脚本，执行以下命令：
SET NOEXEC OFF;
* /


所以，他们真的做打算，这个脚本只能通过SQLCMD运行，无论是通过DOS - > SQLCMD.EXE或PowerShell的 - >调用-SQLCMD

从技术上讲，它可以产生的部署脚本的内容（而非一个字符串流）和操作由该字符串）删除任何结肠癌的命令，和b）替换$（数据库名称）与任何数据库，你打算部署到。不过，我没有试过，我不建议这一点，我不知道它会在什么样的可以通过SQL Server数据工具来生成部署脚本所有情况。但它似乎像的的的选项

此外，minorly相关：你不需要SMO运行SQL脚本。 。SMO与SQL服务器通过对象，而不是通过T-SQL命令直接交互的方式。

编辑：结果
链接在别人试过这个，发现它没有工作：

• 的 http://www.sqlservercentral.com/Forums/Topic1161673-22-1.aspx


• 的https://social .msdn.microsoft.com /论坛/ SQLSERVER / EN-US / 7d3f64b0-f6ed-44ad-99cc-ce4c8324c09e /磨合SQLCMD的命令 - 使用 - smodatabaseexecutenonquery？论坛= sqlsmoanddmo

获取生成发布SQL脚本programmaticaly工作的可能性：

• C＃通过的Process.Start调用 SQLCMD.EXE -i filename.sql ： http://msdn.microsoft.com/en-us/library/system.diagnostics.process.start（v = vs.110）的.aspx


• 通过开源库C＃来处理GO语句和SQLCMD模式冒号命令（当前处理SQL文件，但可以很容易地更新，以接受一个字符串，而不是）： https://github.com/rusanu/DbUtilSqlCmd


• 通过调用-SQLCMD的PowerShell： http://www.sqlservercentral.com/Forums/Topic1502697-1351-1.aspx

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?

解决方案

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

这篇关于ServerConnection.ExecuteNonQuery在SQLCMD模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！