Workaround for datadude deployment bug - NullReferenceException

jamiet — Mon, 21 Nov 2011 08:56:00 GMT

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

Confirmed: Juneau is in the next version of Visual Studio

jamiet — Tue, 20 Sep 2011 21:32:00 GMT

Its no great secret that Juneau, a collection of features in the forthcoming SQL Server codenamed Denali, will be in the next version of Visual Studio but its nice to have it confirmed all the same. I have installed the first publicly available drop of that next version (known as Visual Studio 11 Developer Preview, download from here) that was released at last week's BUILD event and sure enough the Juneau stuff is right there. Here’s some screenshots:

Note the presence of Juneau in the About dialog:

Here’s the new Juneau table designer:

OK, so this isn’t really news (unless you didn’t already know about it) however what is of interest is that this is the Express Edition of Visual Studio. This bodes well for full release – Juneau needs to be available to as many folks as possible which means not making it exclusive to the Ultimate Edition.

@jamiet

SQL Server Development Tools Advisory Program

jamiet — Wed, 13 Apr 2011 21:16:06 GMT

You may have heard that the next version of SQL Server (code-named Denali) will include a new tool (code-named Juneau) that is intended to bring a first-class development experience for T-SQL developers. Juneau is not yet available for us, the general public, to try out so in an attempt to get some early feedback Microsoft have launched the “SQL Server Development Tools Advisory Program” – participants will get access to Juneau releases out-of-band from forthcoming CTPs. From the announcement by Ed Hickey:

Are you a TSQL developer interested in developing database projects for SQL Server Code-Named “Denali” and SQL Azure? Do you want access to the SQL Server Developer Tools, Code-Named “Juneau” (SSDT) engineering team and influence the design of our product going forward? If so, checkout the SSDT Advisory program!

Microsoft is busy preparing the release of SQL Server Developer Tools, Code Name “Juneau”. This exciting new tool will include improvements that are interesting to database developers who are building solutions for both on and off premise databases. Planned features include the following,

Create and edit entities in both connected database instances and offline database projects using a brand new table designer.

A single project type for all supported database server platforms, and instant platform validation for scripts.

Improved TSQL editor with code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform specific validation, debugging and declarative editing.

These are just a few improvement categories that have been discussed publically. There are many more that will be revealed as we move forward.

We are actively seeking TSQL developers who are interested in evaluating all the cool ideas we are toying with. It’s easy to self-nominate, all you need is a Windows Live ID and a non-disclosure agreement with Microsoft. You will be asked to provide some details about your company and your latest database projects for evaluation.

The Advisory program lasts for 6 months. Program participants will have access to SSDT product prototypes in videos and pictures every month, and participate in surveys to give us feedback on the new ideas we are showing.

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/5ee377ef-f24c-4162-97e2-811ab4eb180f

(emphasis is mine)

If you’re interested in getting involved hit the link above and sign-up!

@Jamiet

SSIS Junkie : juneau

Workaround for datadude deployment bug - NullReferenceException

Confirmed: Juneau is in the next version of Visual Studio

SQL Server Development Tools Advisory Program