VS2010 数据库项目部署——“SqlDeployTask"任务意外失败，NullReferenceException [英] VS2010 Database project deploy - "SqlDeployTask" task failed unexpectedly, NullReferenceException

问题描述

我在 Visual Studio 2010 中有一个解决方案，其中包含许多 SQL Server 2008 数据库项目.我正在尝试执行部署解决方案"，但其中一个数据库项目出现以下错误:

I have a solution in Visual Studio 2010 with a number of SQL Server 2008 database projects. I'm trying to do a 'Deploy Solution' and I'm getting the following error for one of the database pojects:

------ Deploy started: Project: MyDBProj, Configuration: Sandbox Any CPU ------
C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(120,5): Error MSB4018: The "SqlDeployTask" task failed unexpectedly.
System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.VariableSubstitution(SqlScriptProperty propertyValue, IDictionary`2 variables, Boolean& isChanged)
   at Microsoft.Data.Schema.Sql.SchemaModel.SqlModelComparerBase.ArePropertiesEqual(IModelElement source, IModelElement target, ModelPropertyClass propertyClass, ModelComparerConfiguration configuration)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareProperties(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareChildren(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareParentElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes, Boolean isComposing)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithoutCompareName(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, ModelComparisonResult result, ModelComparisonChangeDefinition changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareElementsWithSameType(IModelElement sourceElement, IModelElement targetElement, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean ignoreComparingName, Boolean parentExplicitlyIncluded, Boolean compareElementOnly, Boolean compareFromRootElement, ModelComparisonChangeDefinition& changes)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareAllElementsForOneType(ModelElementClass type, ModelComparerConfiguration configuration, ModelComparisonResult result, Boolean compareOrphanedElements)
   at Microsoft.Data.Schema.SchemaModel.ModelComparer.CompareStore(ModelStore source, ModelStore target, ModelComparerConfiguration configuration)
   at Microsoft.Data.Schema.Build.SchemaDeployment.CompareModels()
   at Microsoft.Data.Schema.Build.SchemaDeployment.PrepareBuildPlan()
   at Microsoft.Data.Schema.Build.SchemaDeployment.Execute(Boolean executeDeployment)
   at Microsoft.Data.Schema.Build.SchemaDeployment.Execute()
   at Microsoft.Data.Schema.Tasks.DBDeployTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)
   Done executing task "SqlDeployTask" -- FAILED.
  Done building target "DspDeploy" in project "MyDBProj.dbproj" -- FAILED.
 Done executing task "CallTarget" -- FAILED.
Done building target "DBDeploy" in project "MyDBProj.dbproj" -- FAILED.
Done building project "MyDBProj.dbproj" -- FAILED.

有人知道是什么原因造成的吗?

Does anybody know what could be causing this?

我的项目被配置为创建部署脚本并针对目标数据库运行它.在运行部署之前，我尝试删除目标数据库并创建一个空数据库.我已经尝试在 Visual Studio 中清理"解决方案.

My projects are configured to create the deployment script and run it against the target database. I've tried dropping the target database and creating an empty database before running the deploy. I've tried 'cleaning' the solution in Visual Studio.

推荐答案

我已经能够使用包含单个内嵌函数的测试数据库项目重现此错误，如下所示:

I've been able to reproduce this error with a test database project containing a single in-line function as follows:

CREATE FUNCTION [dbo].[Function1]
()
RETURNS TABLE
AS
    RETURN (
        WITH cte AS (
            SELECT 1 AS [c1]
            FROM [$(Database3)].[dbo].[Table1]
        )
        SELECT 1 AS [c1]
        FROM cte
    )

$(Database3) 是引用另一个数据库项目 .dbschema 文件的数据库变量.这个 dbschema 文件包含一个表 - [Table1].

$(Database3) is a database variable that references another database project .dbschema file. This dbschema file contains a single table - [Table1].

您似乎需要一个带有 CTE 的内联函数，该函数包含对使用数据库变量的另一个数据库的引用.此外，该函数必须已存在于目标数据库中.

It seems that you need an inline function with a CTE that contains a reference to another database using a database variable. Additionally, the function must already exist on the target database.

在某些情况下您可能会收到以下错误(例如内联函数不使用 CTE):

You may get the following error under some circumstances (e.g. the inline function doesn't use a CTE):

------ Deploy started: Project: Database2, Configuration: Debug Any CPU ------
Database2.dbschema(0,0): Warning TSD00560: If this deployment is executed, changes to [dbo].[Function2] might introduce run-time errors in [dbo].[Procedure1].
    Deployment script generated to:
C:\temp\Database2\sql\debug\Database2.sql

    Altering [dbo].[Function2]...
C:\temp\Database2\sql\debug\Database2.sql(74,0): Error SQL01268: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure Function2, Line 9 Invalid object name 'Database3.dbo.Table1'.
    An error occurred while the batch was being executed.
   Done executing task "SqlDeployTask" -- FAILED.
  Done building target "DspDeploy" in project "Database2.dbproj" -- FAILED.
 Done executing task "CallTarget" -- FAILED.
Done building target "DBDeploy" in project "Database2.dbproj" -- FAILED.
Done building project "Database2.dbproj" -- FAILED.

Build FAILED.

所以唯一的解决方法似乎是在部署之前删除目标中的函数.

So the only workaround seems to be to drop the function in the target before the deployment.

我将提出 Microsoft Connect 问题.

I'll raise a Microsoft Connect issue.

更新

我创建了一个连接问题 - https://connect.microsoft.com/VisualStudio/feedback/details/693158/vs2010-database-project-deploy-sqldeploytask-task-failed-unexpectedly-nullreferenceexception

I've created a Connect issue - https://connect.microsoft.com/VisualStudio/feedback/details/693158/vs2010-database-project-deploy-sqldeploytask-task-failed-unexpectedly-nullreferenceexception

这篇关于VS2010 数据库项目部署——“SqlDeployTask"任务意外失败，NullReferenceException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！