Stupid SQL Server 2008 Tricks


THe title is just a nod to David Letterman with his Stupid Pet Tricks or Stupid Human Tricks segments. It is probably not the best title, as will be revelead. A better title:
 
SQL Server 2008 Management Studio renders SQL Server 2005 Management Studio useless when working with projects
 
But "Stupid SQL Server 2008 Tricks" sure fits on the title line much better.
 
Since I have already released the gist of the "problem", I will explain the symptoms.
 

Reproing the Problem

Yesterday, I created a new SQL Server project in SQL Server 2005 manager. After I was finished, the solution explorer showed nothing. Zip, zilch, nada. I then recreated the project, with the same name and got an error.
 
Test-Creation
 
If you want to get the same error, the repro steps are very easy:
 
1. Install SQL Server 2008 as a stand alone (not an upgrade)
2. Open SQL Server 2005 Management Studio
3. Create a project called Test and allow it to create the solution (default)
4. When it appears to do nothing, create a project called Test and allow it to create a solution (default)
 
You can alter steps 3 and 4 and not create the solution folder. As long as the setting is teh same the second time, you will get the error.
 
I then looked in Windows Explorer and yes, there was a project there (Test.ssmssqlproj), but there was no solution file. Strange. So, I tried to open the project file. Still nothing in SQL Management studio.
 
But, then I had an epiphany. Try opening the project in SQL Server 2008 Management Studio. Yes, it works.
 

Incompatibility of Tools

What this boils down to is there are some incompatibility with the underlying assemblies/DLLs that SQL Management studio uses to work its magic. In addition to this incompatibility, I have found a few more.

When working with a SQL Server 2005 instance, in SQL 2008 Management Studio, you cannot alter the definition of a table. Attempting to alter a SQL 2005 table in SQL 2008 Management Studio, even one you created with SQL 2008 Management Studio, fails with this largely useless error:

Useless-Error

Now, looking at this, you might think. Yeah, the error box seems useless, but it allows you to save it as a text file. That sounds like it might be a cool feature, if you are thinking, as I did, that the text file is a drop and recreate script for the table. If you were thinking that, you and I were both wrong. Here is what the text file contains:

/*
   Friday, August 22, 20089:17:05 AM
   User:
   Server: BNA-GBEAMER2
   Database: UnitWarehouse
   Application:
*/

SimHistory

Now if you first thought is "that is a completely useless text file", you are I are tracking. If I were doing a lot of work and saved this file off and then came back a few weeks later to look at the file (unlikely, but let’s play along), would it mean ANYTHING to me? I can picture this "Greg, what the *#&% were you doing on August 22 at 9:17 AM with the SimHistory table?" (I only know this because I know my schema). Worse, imagine someone else looking at this text file. It might make sense if the file contained something like this:

/*
   Friday, August 22, 20089:17:05 AM
   User:
   Server: BNA-GBEAMER2
   Database: UnitWarehouse
   Application: 
   Object: SimHistory

*/

Attempt to alter object in Management Studio failed as saving changes to SQL Server 2005 objects is not permitted.

While saving off this file is still useless to me, it at least logs something.

What This Means

To me, the interesting point of this finding is it means I have three choices with SQL Server 2008.

1. Switch all of my databases to 2008 and only use SQL Server 2008 tools
2. Get rid of SQL 2008 completely until I am ready for it
3. Use SQL 2008 MS for my projects and SQL 2005 MS for my visual schema work

Number 1 is not an option until I know SQL 2008 databases can be attached on SQL 2005, as I have SQL 2005 database servers. I would suspect there is some incompatibility here due to the error altering schema. I could be wrong, as this just could be a tool/database impedence match. I do not have the time to investigate, so I will be pessimistic and safe.

Number 2 is a risky option now that I have a SQL 2008 instance installed, as I may end up with a hefty reinstall for 2005 to fix the tools. I cannot afford this time either, although not installing 2008 at all might have been a good option at one time. Unfortunately, I have to investigate 2008 for my job.

Number 3 is the sanest option right now, although it does require keeping both open. As long as I have 2008 open with a solution, it is easy to tell them apart. Since I am only working with it for projects, this is not a horrible ‘trade off’.

Peace and Grace,
Greg

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: