sql-server-data-tools - 使用 DacServices.Deploy 发布 DACPAC (SSDT) 期间的执行超时错误

Question

在 C# 中使用 DacServices.Deploy 发布 dacpac 时，我们随机收到以下错误。除了表格，Dacpac 还有一个部署后脚本，它是引发超时错误的部分。我们正在发布到新的 SQL Server 2014 数据库（因此，不是升级方案）。有人会提出解决方案吗？

PS：这通常发生在第一次尝试新服务器时，然后在第二次尝试时完美运行。DacPac 发布是我们安装程序的一部分，安装程序失败会给人留下不好的印象。

错误...

执行超时已过期。在操作完成之前超时时间已过或服务器没有响应。

我们已经明确设置了超时，但似乎没有被采纳，因为发布在一分钟多一点后失败，而超时设置为 600 秒。

DacDeployer 代码...

                var dacOptions = new DacDeployOptions();
                dacOptions.IncludeCompositeObjects = true;
                dacOptions.IncludeTransactionalScripts = true;
                dacOptions.GenerateSmartDefaults = true;
                //Specify timeout in seconds to override the default 60
                dacOptions.CommandTimeout = 600;

                using (DacPackage dacpac = DacPackage.Load(dacpacName))
                {
                    dacServiceInstance.Deploy(dacpac, DBName, upgradeExisting: true, options: dacOptions);
                }

DacServices.Deploy 上的事件侦听器返回的信息、警告和错误日志...

Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating DW_Control...
Creating [DW_INTERNAL]...
Creating [DW_INTERNAL].[DB_VERSIONS]...
Creating [DW_INTERNAL].[SCRIPT_LOG]...
Warning! The maximum key length is 900 bytes. The index 'PK_SCRIPT_LOG' has maximum length of 1102 bytes. For some combination of large values, the insert/update operation will fail.
Creating [DW_INTERNAL].[SCRIPT_MASTER]...
Creating [DW_INTERNAL].[SCRIPT_STATUS]...
Warning! The maximum key length is 900 bytes. The index 'PK_SCRIPT_STATUS' has maximum length of 1094 bytes. For some combination of large values, the insert/update operation will fail.
Creating [dbo].[ARCHIVE_STATUS]...
Creating unnamed constraint on ...
Creating [dbo].[FK_...]...
Creating [dbo].[VW_...]...
Creating [DW_INTERNAL].[ADD]...
Creating [DW_INTERNAL].[ADD_SCRIPT]...
Creating [DW_INTERNAL].[UPGRADE]...
Creating [dbo].[VW_AMOUNT_SUMMARY].[MS_Description]...
The transacted portion of the database update succeeded.
.Net SqlClient Data Provider: Msg -2, Level 11, State 0, Line 0 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Script execution error.  The executed script:
EXECUTE DW_INTERNAL.ADD_VERSION @Module = 'CTL', @ToVersion = '1.17.02.00';

EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '0.00.00.00', @ToVersion = '1.17.02.00', @ApplyOrder = 40, @UpgrScriptFilepath = 'CTL_0.00.00.00_1.17.02.00_P_DML.sql';

EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '0.00.00.00', @ToVersion = '1.17.02.00', @ApplyOrder = 30, @UpgrScriptFilepath = 'CTL_0.00.00.00_1.17.02.00_R_DML.sql';

EXECUTE DW_INTERNAL.ADD_VERSION @Module = 'CTL', @ToVersion = '1.17.07.00';

EXECUTE DW_INTERNAL.ADD_SCRIPT @Module = 'CTL', @FromVersion = '1.17.02.00', @ToVersion = '1.17.07.00', @ApplyOrder = 60, @UpgrScriptFilepath = 'CTL_1.17.02.00_1.17.07.00_P_DML.sql';

EXECUTE DW_INTERNAL.ADD_SCRIPT @M
An error occurred while the batch was being executed.
Updating database (Failed)
Error details: Could not deploy package.
Error SQL72014: 

Stack Trace: at Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
   at DacDeployer.Program.Main(String[] args)

---

存储过程调用...

CREATE PROCEDURE [DW_INTERNAL].[ADD_SCRIPT]
    @Module VARCHAR(30), 
    @FromVersion VARCHAR(20), 
    @ToVersion VARCHAR(20), 
    @ApplyOrder INT, 
    @UpgrScriptFilepath VARCHAR(1024) 

AS
BEGIN
    DECLARE @FileName VARCHAR(500);
    SET @FileName = RIGHT(@UpgrScriptFilepath, CHARINDEX('\', REVERSE(@UpgrScriptFilepath)) - 1);

    IF NOT EXISTS (SELECT 1 FROM [DW_INTERNAL].[SCRIPT_MASTER] WHERE [MODULE]=@Module AND [FROM_VERSION]=@FromVersion AND [TO_VERSION]=@ToVersion and RIGHT([UPGR_SCRIPT_FILEPATH], CHARINDEX('\', REVERSE([UPGR_SCRIPT_FILEPATH])) - 1) = @FileName)
    BEGIN
        INSERT INTO [DW_INTERNAL].[SCRIPT_MASTER]
                   ([MODULE],
                    [FROM_VERSION],
                    [TO_VERSION],
                    [APPLY_ORDER],
                    [UPGR_SCRIPT_FILEPATH])
             VALUES
                   (@Module,
                   @FromVersion,
                   @ToVersion,
                   @ApplyOrder,
                   @UpgrScriptFilepath)
    END
    ELSE
    BEGIN
        UPDATE [DW_INTERNAL].[SCRIPT_MASTER]
        SET [APPLY_ORDER] = @ApplyOrder
        WHERE [MODULE]=@Module AND [FROM_VERSION]=@FromVersion AND [TO_VERSION]=@ToVersion and [UPGR_SCRIPT_FILEPATH]=@UpgrScriptFilepath
    END
END

标签： sql-server-data-toolsdacpac