Cannot Open Database Project

Today I opened visual studio and got an error message.

".dbproj files cannot be opened because its project type(.dbproj) is not supported by this version of the application"

I reinstalled the service pack and then reinstalled visual studio and it I still couldn't open my database project and all the packs and it was still not working.

So I turned to google and found out that all I had to do was reset the dev environment by running the following command.

devenv /resetskippkgs

If only i have of known this before I spent half the day reinstalling everything.

Single Quotes in TSQL

Recently I had a to format some data, originally the formatting was done using the MVC.NET page but there was one for small problem it took over five minutes to return 2,000 records which is just a little bit to long for my liking.

We only had a day to fix this problem so it was time for a dirty yet brilliant hack. I created a table variable and for the formatted data added the data to it and returned the date all in one stored procedure, there was one problem tho I needed to generate T-SQL and run it using EXECUTE (I know its not the best way to do things). I was working away at this and then a problem popped up there is no way to escape single quotes that I could work out in T-SQL.

But with a little trickery.....

DECLARE @singleQuote CHAR(1);
SET @singleQuote = CHAR(39);

Problem Solved!

How I Fixed - SQL Server Error 310

I have just spent an hour trying to work out why my Sql Server Agent would not start. I kept getting this error message

SQLServerAgent could not be started (reason: Unable to connect
to server'(local)'; SQLServerAgent cannot start).

It was making me really angry when I realised what was wrong, I did not have the account for the Windows user the agent was running under in my database to. I created an account and gave it full access to the db and now everything is going fine.

P.S. Yes I know i am an idiot.

How to Move the Default Databases for MS SQL Server

I have juss finshed the painful task of moving of moving all the default databases (Master.mdf etc) from program files to more useful places on the database server. I wrote my self a little guide in google docs and thought someone else might like to use it. Here is the guide.