20 Dec 2006

MSBuild using SQL Script parameters

I have been working with MSBuild and the Microsoft.Sdc.Tasks to deploy my current project’s databases.  Some of the SQL scripts that are executed within MSBuild need parameters to be set to create the database objects, which the Microsoft.Sdc.Tasks.Sql.Execute method supports.  To use parameters within MSbuild and SQL.Execute; firstly create an item group (<ItemGroup>) then create element of any name as this will become the name of the item that can be referred to in MSbuild.  Within the new element an attribute has to set called “Include”, then create two other elements called “name” and “value”.  Here is an example:

<ItemGroup>

            <schemaName Include=”true”>

<name>@pTableName</name>

<value>’sys.Objects’</value>

            </schemaName>

            <schemaTable Include=”true”>

<name>@pTableId</name>

<value>342</value>

            </ schemaTable >

</ItemGroup>

 

The name element needs to be set to the name of the parameter including the @.  The value element needs to be set with the value to which the parameter has to be set to. The only gotach I have found so far is that string data needs to wrapped in the quotes. 

 

Then to the get the MSBuild to pass the parameter into the Sql.Execute Task set a attribute on the task called Parameters and then @(<ItemName>).  The Parameters will take in a list of parameters.  Here is an example:  <Sql.Execute Path="CreateTable.sql" ServerName="$(SQLServer)" DatabaseName="$(Database)" Parameters="@( schemaName);@( schemaTable)" />

To Collate or not to Collate

Collation plays an important role within the database, as it sets out how the SQL Server has to manage string data within storage & queries, yet I don’t script out the collation when scripting out my databases.  So why don’t I script out the collation if it so important? Its because if the collation is specified but not used properly within the database it can cause some of most common problems, like “Cannot resolve collation conflict for EQUAL TO operation” or “Cannot resolve collation conflict for UNION operation”. These errors are caused when string data within different collations are being compared or combined into the same result set; this is nothing new so why talk about collation now? Well it’s to do with the release of Visual Studio 2005 Team Edition for Database Professionals (VSTDB) something I think needed to be taken in consideration if this is being used to manage the database side of the project.

 

A cause for theses types of problem is when a table is created on a database with the collation scripted out and deployed on another database which has a different collation to the create script.  With the VSTDB this will not occur as much because a project will be assigned a collation and the database will be created with this collation or if the database does not get recreated and has a different collation then the tables will be created with the project collation (This is the desired behavior when I saw this last and was confirmed when I spoke to the VSTDB Team in November). So I can see that VSTDB will help with reducing these types of errors.

 

Another cause of the error is when creating temp tables without setting the collation to the same as the database collation and the server collation is different.  There are 2 ways to resolve this issue. The first is to rebuild your system tables to match the collation of the database that has been created or another way is by using the collation clause within the create temp table script(s) within the T-SQL code.  These errors could be become more frequent when using the VSTDB; because as mentioned before the database or tables that are create under the VSTDB are assigned the project collation, which will have to be of a type collation as there is no server default option and this could be different to that of the server that is being deployed to. 

 

By taking the time to put in the extra collation clause in the temp table script(s) it reduce the risk of database code failing with “cannot resolve collation” errors on any SQL server configuration.  Also it reduce the temptation of people changing the VSTDB project collation to match there server collation if they can’t change their SQL server default collation and causing different behavior of SQL statements.

 

I can understand the reason for making the database project ask for a collation because it means that when you unit test the code on any server it will always behave in the same way. Also it means as stated before, the database can run on any server with less risk of collation errors.

Delpoying Reporting Services Reports With MSBuild

I am currently responsible for deploying our project into the test and production environments.  The project has a few reports which need to be installed on a few report servers.  The deployment tool that I am using for the rest of my project is MSBuild so I tried to see if there was a way of getting MSBuild to deploy the reports.  The only method that I found was to write an RS script file which deployed the reports and calls the Exec task to run the RS script. 

So I started outright with RS script as I was not over joyed about writing VB.Net code as I am a C# coder. Then I remembered that MSBuild is customizable as it allows custom tasks to be written.  So I stopped writing my VB.Net code went back C# and created a few tasks that are commonly needed to deploy: AddReportUser, CreateReportFolder, CreateConnectionSoruces, DeployReports and SetReportsDataSource.  The task code was easy to write. I did have to change some of the auto generated code.  I changed the refence.cs file by changing the ReportingService2005 method to accept a string as it called and removed all references to the setting file and app.config.  I also deleted the setting file and app.config that was created by the Visual Studio.