THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Workaround for datadude deployment bug - NullReferenceException

I have come across a bug in Visual Studio 2010 Database Projects (aka datadude aka DPro aka Visual Studio Database Development Tools aka Visual Studio Team Edition for Database Professionals aka Juneau aka SQL Server Data Tools) that other people may encounter so, for the purposes of googling, I'm writing this blog post about it. Through my own googling I discovered that a Connect bug had already been raised about it (VS2010 Database project deploy - “SqlDeployTask” task failed unexpectedly, NullReferenceException), and coincidentally enough it was raised by my former colleague Tom Hunter (whom I have mentioned here before as the superhuman Tom Hunter) although it has not (at this time) received a reply from Microsoft. Tom provided a repro, namely that this syntactically valid function definition:

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
)

would produce this nasty unhelpful error upon deployment:

C:\Program Files (x86)\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 "Lloyds.UKTax.DB.UKtax.dbproj" -- FAILED.
 Done executing task "CallTarget" -- FAILED.
Done building target "DBDeploy" in project

It turns out there are a certain set of circumstances that need to be met for this error to occur:

  • The object being deployed is an inline function  (may also exist for multistatement and scalar functions - I haven't tested that)
  • That object includes SQLCMD variable references
  • The object has already been deployed successfully

Just to reiterate that last bullet point, the error does not occur when you deploy the function for the first time, only on the subsequent deployment.

 

Luckily I have a direct line into a guy on the development team so I fired off an email on Friday evening and today (Monday) I received a reply back telling me that there is a simple fix, one simply has to remove the parentheses that wrap the SQL statement. So, in the case of Tom's repro, the function definition simply needs to be changed to:

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
--)

I have commented out the offending parentheses rather than removing them just to emphasize the point.

Thereafter the function will deploy fine. I tested this out on my own project this morning and can confirm that this fix does indeed work.

 

I have been told that the bug CAN be reproduced in the Release Candidate (RC) 0 build of SQL Server Data Tools in SQL Server 2012 so am hoping that a fix makes it in for the Release-To-Manufacturing (RTM) build.

Hope this helps

@jamiet

Published Monday, November 21, 2011 9:56 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

JJ said:

thanks jamiet, great information! datadude is really great but specially when you work extensive with different type of functions prepare for  workarounds. machine has to follow developer or developer has to follow machine!? ;) See also this topic for another "bug" good to know the workaround (use MSTVF instead ITVF):

Missing Dependency links between SP and function

http://social.msdn.microsoft.com/Forums/de-DE/vstsdb/thread/14db81c3-6734-4af3-826e-8130e85e3c38

November 21, 2011 7:08 PM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM
 

HarmenW said:

I also had this problem, but removing the () brackets did completely solve it at first. I also needed to remove the ; at the end of the query.

So in your example, if you have a ; at the end:

 FROM cte;

it should be:

 FROM cte

Than it worked for me.

November 15, 2013 5:27 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement