A while ago on the project I’ve been working on recently, I was having issues with my development environment which for some reason had stopped wanting to deploy our database project to my newly created sql server 2008 instance.
This was on a new build of a machine that I had previously used, but had decided to upgrade the operating system on. As part of this I obviously had to reinstall all the client software again, and this time I had chosen to have a named instance of sql server 2008 which unfortunately meant that the usual ‘.’ or ‘(local)’ connection strings stopped working.
At first the errors I was seeing on the new build were as follows…
Build FAILED.
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.Data.Schema.SqlTask
s.targets(120,5): error MSB4062: The "SqlDeployTask" task could not be loaded from the assembly Mic
rosoft.Data.Schema.Tasks, Version=9.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a. Could
not load file or assembly 'Microsoft.Data.Schema.Tasks, Version=9.1.0.0, Culture=neutral, PublicKey
Token=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. Conf
irm that the <UsingTask> declaration is correct, and that the assembly and all its dependencies are
available.
This first error was simply a matter of reinstalling the Microsoft Visual Studio Team System 2008 Database Edition GDR R2 which you can download from the following location http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en.
However, even after re-installing this, I was still receiving deployment issues. This was because the database project was set up to deploy to the default named instance of sql server (i.e. SERVER=’.’ ), but as I had re-installed sql server 2008 on my machine, and now had a fully named instance of sql 2008 called MSSQLSERVER2008, this deployment failed as it couldn’t open the connection.
A colleague of mine ( Steve Wright ), then gently pointed out that you can actually isolate the connection string for a database deployment when using GDR R2, in the project options. The trick is to select the Configure deployment settings for, and choose the My Isolated development environment option from this drop down list. This will then allow your deployment to deploy correctly to your local target database, but without the knock on effect of messing up the deployment settings for every other team member on your project. I believe this setting gets stored in a local user options file rather than checked into source control as part of the .dbproj file.
After setting all this correctly, the database deployment went ahead smoothly. Unfortunately a while later someone added defaulted column to a database table with a default calculated value determined at runtime, by the SQL in built function of suser_name(). This returned my computer name + ‘\’ + my username, which unfortunately exceeded the allowed size for this table column!