VS2010 Database project deploy - “SqlDeployTask” t

Question

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?

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.

Answer 1

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) is a database variable that references another database project .dbschema file. This dbschema file contains a single table - [Table1].

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.

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.

I'll raise a Microsoft Connect issue.

UPDATE

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

Answer 2

I have encountered similar NullReference exception attached below when had the following scenario:

1. I've edited the definition of partition scheme PS1 of SQL Database project and all tables which were using it (T1, T2, T3)
2. However, in my database, I had an old table not defined in the code (T_old) but still not deleted from the database (it was not used anymore, but dacpac doesn't remove stuff for you, only changes or adds). This old table was using the same partition scheme, but dacpac didn't have any reference to that old table definion meaning it was not able to backup table T_old to change PS1

You can have similar issues when referencing objects that changed by old objects that are not defined in code anymore.

To solve that, check the old dependencies of the object and delete them. Try to change things only in code and not mix it with ad-hock changes in database.

Hope that will help, since error message doesn't give a lot of explanation.

Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object.
    at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.UnbindTableDatamotion(SqlTable sourceTable, SqlTable targetTable, Boolean unbindPartitionScheme, HashSet`1 unboundColumns)
    at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateUnbindTableSteps(SqlTable sourceTable, SqlTable targetTable)
    at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateSteps(Int32 operation, IModelElement element)
...

Answer 3

Tom, I've documented a workaround (and a very easy one at that) here: http://sqlblog.com/blogs/jamie_thomson/archive/2011/11/21/workaround-for-datadude-deployment-bug.aspx