Steven Wright Blog

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

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

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