Archive for the ‘Database’ Category

Using MSBuild to publish a VS 2012 SSDT .sqlproj database project the same way as a VS 2010 .dbproj database project (using command line arguments to specify the database to publish to)

March 18th, 2013 20 comments

Post and code updated on March 21, 2013, and again on March 22, 2013.

We recently upgraded from VS (Visual Studio) 2010 to VS 2012, and with it had to upgrade our .dbproj database project to a .sqlproj.  When making the switch I realized that .sqlproj database projects do not support specifying the database to deploy to as MSBuild command line arguments; instead you have to pass in the path to an xml file that has the necessary information.

So with the old .dbproj database project, you could deploy it to a database using:

MSBuild /t:Deploy /p:TargetDatabase="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:DeployToDatabase="True" "[PathToBranch]Database\Database.dbproj"

But with the new .sqlproj database project you have to do:

MSBuild /t:Publish /p:SqlPublishProfilePath="myPublishFile.publish.xml" "[PathToBranch]Database\Database.sqlproj"

Where “myPublishFile.publish.xml” contains the database server and name to publish to.

One other minor thing to note is that it is called “deploying” the database with .dbproj, and is called “publishing” the database with .sqlproj; so when I say Deploy or Publish, I mean the same thing.

We use TFS at my organization and while making new builds for our Test environment, we have the build process deploy the database solution to our various Test databases.  This would mean that for us I would either need to:

1 – create a new [DbName].publish.xml file for each database, check it into source control, and update the build template to know about the new file, or

2 – update the file contents of our myPublishFile.publish.xml file dynamically during the build to replace the Database Name and Server in the file before publishing to the database (i.e. read in file contents, replace string, write file contents back to file, publish to DB, repeat).

Option 1 means more work every time I want to add a new Test database to publish to.  Option 2 is better, but still means having to update my TF Build template and create a new activity to read/write the new contents to the file.

Instead, there is a 3rd option, which is to simply add the code below to the bottom of the .sqlproj file.  This will add some new MSBuild targets to the .sqlproj that will allow us to specify the database name and connection string using similar MSBuild command line parameters that we used to deploy the .dbproj project.

The code presented here is based on this post, but the author has closed the comments section on that post and has not replied to my emails about the bugs in his code and example, so I thought I would share my modified and enhanced solution.

	Custom targets and properties added so that we can specify the database to publish to using command line parameters with VS 2012 .sqlproj projects, like we did with VS 2010 .dbproj projects.
	This allows us to specify the MSBuild command-line parameters TargetDatabaseName, and TargetConnectionString when Publishing, and PublishToDatabase when Building.
	I also stumbled across the undocumented parameter, PublishScriptFileName, which can be used to specify the generated sql script file name, just like DeployScriptFileName used to in VS 2010 .dbproj projects.
	Taken from:
  <PropertyGroup Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''">
    <PublishToDatabase Condition="'$(PublishToDatabase)' == ''">False</PublishToDatabase>
    <TargetConnectionStringXml Condition="'$(TargetConnectionString)' != ''">
      &lt;TargetConnectionString xdt:Transform="Replace"&gt;$(TargetConnectionString)&lt;/TargetConnectionString&gt;
    <TargetDatabaseXml Condition="'$(TargetDatabaseName)' != ''">
      &lt;TargetDatabaseName xdt:Transform="Replace"&gt;$(TargetDatabaseName)&lt;/TargetDatabaseName&gt;
        &lt;Project xmlns:xdt="" xmlns=""&gt;
    <SqlPublishProfilePath Condition="'$([System.IO.Path]::IsPathRooted($(SqlPublishProfilePath)))' == 'False'">$(MSBuildProjectDirectory)\$(SqlPublishProfilePath)</SqlPublishProfilePath>
    <!-- In order to do a transform, we HAVE to change the SqlPublishProfilePath -->
    <TransformStackTraceEnabled Condition="'$(TransformStackTraceEnabled)'==''">False</TransformStackTraceEnabled>
  <Target Name="AfterBuild" Condition="'$(PublishToDatabase)'=='True'">
    <CallTarget Targets="Publish" />
  <UsingTask TaskName="ParameterizeTransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\Web\Microsoft.Web.Publishing.Tasks.dll" />
  <Target Name="BeforePublish" Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''">
    <Message Text="TargetDatabaseName = '$(TargetDatabaseName)', TargetConnectionString = '$(TargetConnectionString)', PublishScriptFileName = '$(PublishScriptFileName)', Transformed Sql Publish Profile Path = '$(TransformOutputFile)'" Importance="high" />
    <!-- If TargetDatabaseName or TargetConnectionString, is passed in then we use the tokenize transform to create a parameterized sql publish file -->
    <Error Condition="!Exists($(SqlPublishProfilePath))" Text="The SqlPublishProfilePath '$(SqlPublishProfilePath)' does not exist, please specify a valid file using msbuild /p:SqlPublishProfilePath='Path'" />
    <ParameterizeTransformXml Source="$(SqlPublishProfilePath)" IsSourceAFile="True" Transform="$(TransformPublishXml)" IsTransformAFile="False" Destination="$(TransformOutputFile)" IsDestinationAFile="True" Scope="$(TransformScope)" StackTrace="$(TransformStackTraceEnabled)" SourceRootPath="$(MSBuildProjectDirectory)" />


So after adding this code at the bottom of the .sqlproj file (above the </Project> tag though), you can now build and publish the database solution from the MSBuild command line using:

MSBuild /t:Build /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishToDatabase="True" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

Here you can see the 3 new parameters that we’ve added being used: TargetDatabaseName, TargetConnectionString, and PublishToDatabase.

When the TargetDatabaseName or TargetConnectionString parameters are provided we generated a new transformed .publish.xml file, which is the same as the provided “Template.publish.xml” file, but with the database and connection string values replaced with the provided values.

The PublishToDatabase parameter allows us to publish to the database immediately after the project is built; without this you would have to first call MSBuild to Build the database project, and then call MSBuild again to Publish it (or perhaps using “/t:Build;Publish” would work, but I didn’t test that).

If you want to simply publish the database project without building first (generally not recommended), you can do:

MSBuild /t:Publish /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

Be careful though, since if you don’t do a Build first, any changes that have been made since the last time the .sqlproj file was built on your machine won’t be published to the database.

Notice that I still have to provide a path to the template publish.xml file to transform, and that the path to this file is relative to the .sqlproj file (in this example the Template.publish.xml and .sqlproj files are in the same directory).  You can simply use one of the publish.xml files generated by Visual Studio, and then the TargetDatabaseName and TargetConnectionString xml element values will be replaced with those given in the command line parameters.  This allows you to still define any other publish settings as usual in the xml file.

Also notice that the PublishToDatabase parameter is only used when doing a Build, not a Publish; providing it when doing a Publish will not hurt anything though.

While creating my solution, I also accidentally stumbled upon what seems to be an undocumented SSDT parameter, PublishScriptFileName.  While the DeployScriptFileName parameter could be used in VS 2010 .dbproj projects to change the name of the generated .sql file, I noticed that changing its value in the .publish.xml file didn’t seem to have any affect at all (so I’m not really sure why Visual Studio puts it in there).  I randomly decided to try passing in PublishScriptFileName from the command line, and blamo, it worked!  I tried changing the <DeployScriptFileName> element in the .publish.xml file to <PublishScriptFileName>, but it still didn’t seem to have any effect.

So now if I wanted to deploy my database project to 3 separate databases, I could do so with the following code to first Build the project, and the Publish it to the 3 databases:

MSBuild /t:Build "[PathToBranch]\Database\Database.sqlproj"
MSBuild /t:Publish /p:TargetDatabaseName="[DbName1]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName1].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
MSBuild /t:Publish /p:TargetDatabaseName="[DbName2]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName2].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
MSBuild /t:Publish /p:TargetDatabaseName="[DbName3]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName3].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

You could also instead just call MSBuild using the Build target with the PublishToDatabase parameter (which might actually be the safer bet); whatever you prefer.  I have found that once the database project is built once, as long as no changes are made to it then subsequent “builds” of the project only take a second or two since it detects that no changes have been made and skips doing the build.

If you have any questions or feedback, let me know.

Happy coding!

Path Too Long for Team Foundation Database Project Build

February 5th, 2012 1 comment

Arrggghhhh TFS and builds!  Such a love-hate relationship!  So we have our TFS builds setup to both compile our C# projects as well as compile and deploy our Team Foundation (TF) Database (DB) projects.  One day I started getting the following file path too long error message on our build server:

$/RQ4TeamProject/Prototypes/BuildProcessTests/RQ4.Database.sln – 1 error(s), 69 warning(s), View Log File
C:Program Files (x86)MSBuildMicrosoftVisualStudiov10.0TeamDataMicrosoft.Data.Schema.TSqlTasks.targets (80): The "SqlSetupDeployTask" task failed unexpectedly. Microsoft.Data.Schema.Build.BuildFailedException: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters. —> System.IO.PathTooLongException: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.    at System.IO.PathHelper.Append(Char value)    at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength)    at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath)    at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)    at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)    at System.IO.StreamReader..ctor(String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize)    at System.IO.StreamReader..ctor(String path, Boolean detectEncodingFromByteOrderMarks)    at Microsoft.Data.Schema.Sql.Build.SqlPrePostDeploymentModifier.GenerateMergedSqlCmdFiles(DeploymentContributorConfigurationSetup setup, DeploymentContributorConfigurationFile configFile)    at Microsoft.Data.Schema.Sql.Build.SqlPrePostDeploymentModifier.OnEstablishDeploymentConfiguration(DeploymentContributorConfigurationSetup setup)    at Microsoft.Data.Schema.Build.DeploymentContributor.EstablishDeploymentConfiguration(DeploymentContributorConfigurationSetup setup)    — End of inner exception stack trace —    at Microsoft.Data.Schema.Build.DeploymentContributor.EstablishDeploymentConfiguration(DeploymentContributorConfigurationSetup setup)    at Microsoft.Data.Schema.Build.DeploymentProjectBuilder.VerifyConfiguration()    at Microsoft.Data.Schema.Tasks.DBSetupDeployTask.BuildDeploymentProject(ErrorManager errors, ExtensionManager em)    at Microsoft.Data.Schema.Tasks.DBSetupDeployTask.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)

Naturally I said, "Ok, our TF DB project isn’t compiling because a path is too long. Somebody must have checked in a stored procedure with a really long name".  After viewing the history of the branch I was trying to build however, I didn’t see anything that stuck out.  So for fun I thought I would shorten the Build Definition name’s length and build again.  Viola, like most path issues with TFS this fixed the issue (this is because the build definition name is often used in the path that TFS moves/builds files to).  However, we have many queries setup that match the specific Build Definition name (since it’s used in the "Integrated in Build" work item value), so shortening it wasn’t a long term solution.  As an added frustration bonus, I discovered our build definition name was only 1 character too long!

The first thing I did was make a Path Length Checker program so I could see how long the file paths (files and directories) really were on the build server.  Oddly enough, the longest paths were 40 characters short of the maximum limit described by the error message.

So I took a look at our database folder structure and saw that it really was wasting a lot of characters.  This is what the path to one of our stored procedure folders looks like: "..DatabaseSchema ObjectsSchemasdboProgrammabilityStored ProceduresProcs1".  I figured that I would just rename some of these folders in Visual Studio and that should be good……..OMG never try this while connected to TFS!  I got a popup warning for every single file under the directory I was renaming (thousands of them), with something along the lines of "Cannot access file X, or it is locked…..blah blah. Please press OK".  So after holding down the enter key for a over an hour to get past all these prompts it finally finished.  When I reviewed the changes to check in, I saw that many duplicate folders had been created, and there were miscellaneous files all over the place; some got moved, some never; what a mess.  So I went ahead and reverted my changes.

So I thought, "Ok, let’s try this again, but first going offline so as not to connect to TFS".  So I disabled my internet connection and opened the database solution (this is the only way that I know of to work "offline" in TFS 🙁 ).  I then tried to change the high level folder "Schema Objects" to just "Schema".  Nope, Visual Studio complained that the folder was locked and couldn’t be changed.  I thought to myself, "TFS makes all non-checked out files read-only, and I’m offline so it can’t check them out.  That must be the problem".  So I opened up explorer and made all of the files and folders writable and tried again. Nope, no deal; same error message.

So I thought, "Alright, let’s try doing a low level directory instead".  It seems that VS would only let me rename a directory that didn’t contain other directories.  So I renamed the "Procs1" folder to just "1".  I no longer got the warning prompt for every file, but it was still pretty slow and I could watch VS process every file in the Solution Explorer window.  After about 10 minutes it finally finished.  So I checked in my changes and tried building again.  Nope, same error message as before about the path being too long.

So I said screw this.  I opened up the TFS Source Control Explorer and renamed the folder from there.  It worked just fine.  I then had to open up the Database.dbproj file in a text editor and do a find and replace to replace "Schema Objects" with "Schema".  This worked for refactoring the folder structure quickly, but I was still getting the "path too long" error message on the build server. Arrrrgg!

So I went back to the build, set the verbosity to “diagnostic” and launched another build (which failed again with the path too long error).  Looking through the error message I noticed that it did complete building the DB schema, and went on to failing on building the Pre/Post deployment scripts.  Looking back to my original error message and reading it more carefully I noticed this line, “Microsoft.Data.Schema.Sql.Build.SqlPrePostDeploymentModifier.GenerateMergedSqlCmdFiles”.  So now I was pretty sure the problem was in the pre and post deployment scripts.

Now, we have a very custom process for our database scripts, and part of this process involves using SQLCMD mode to include other script files into our pre and post deployment files when they are generated; it basically makes it look like the referenced script’s contents were in the pre/post deployment script the entire time.  This is necessary for us so that developers don’t have to look through pre and post deployment scripts that are tens of thousands of lines long.  It turns out that while none of these referenced script files themselves had a path that was over the limit, somehow during the generation of the pre/post deployment scripts it was making the path even longer.  I looked through our referenced scripts and saw a few particularly long ones.  So I refactored them to shorten the file names, and presto the build worked!  Hooray!

I’m guessing that the reason the build wouldn’t give me an actual filename when it encountered the error is because SQLCMD mode was dynamically referencing those scripts at build time, so to the build it just looked like the pre and post deployment scripts were each thousands of lines long, when in fact they are only maybe 50 lines long, but they "include" other files, and those file references must be used at build time.

So the morals of this story are:

1. If VS is blowing chunks when you try to rename a folder (especially when connected to TFS), don’t do it through VS.  Instead modify the folder structure outside of VS and then manually edit the .csproj/.dbproj/.vbproj files to mirror the changes.

2. Whenever you are stumped on a build error, go back and THOROUGHLY read the ENTIRE error message.

3. Be careful when using compile-time language features to reference/include external files.