SQL: 6.5 Compatibility in SQL Server 2005

Filed under: SQL Server, Tech Stuff — Written by Chrissy on Monday, May 15th, 2006 @ 11:10 pm

This isn't a very common scenario, but I figured I'd write about it anyway. We recently implemented a Windows 2003 Active Directory domain at work. I suggested to my employer that, going forward, we only install SQL Server 2005 when we need a database server (phasing out 2000). I'd also suggested consolidating a couple of our older servers (7 and 2000) into one.

So I setup a test SQL Server 2005 server in our lab environment. I migrated a few of our most active databases using two different methods: detach -> reattach and backup -> restore. Both worked perfectly well. Actually, the detach -> reattach complained about possible corrupt indexes but it was a false alarm. Other than that, things went very smoothly.

Apparently, one of the databases I migrated has been around for quite some time. It showed up as SQL 6.5 Compatible and lacked most of the options in the Management Studio.

SQL Server 6.5 Compatibility mode in SQL Server 2005 Management Studio

I assumed that this particular database started out on a SQL 6.5 machine which was then upgraded to SQL 7 and SQL 7 kept it in 6.5 compatibility mode. Still, the database worked and I was able to issue a few queries to it without a problem. I wondered if perhaps SQL Server 7 or 2000's Enterprise Manager would allow me to manage that SQL 6.5 database in the GUI but I couldn't even connect.. SQL 2005 requires that you connect via the management studio to administer the machine graphically. Query Analyser connected just fine though it took a little bit of tweaking from the SQL 7 machine. Initially, I ran into this error:

Named Pipes Connection Broken

Named Pipes Connection Broken. Alright, we'll avoid NetBIOS and try by IP.. perhaps that would force a TCP/IP connection. No dice. It just forced a TCP/IP over NetBIOS connection. I thought back to the days of SQL 7 and did the following, which worked: Start -> Programs -> SQL Server 7 -> Client Network Utility -> Add -> Server Alias: labsql2k5 | Network Library: TCP/IP | Server Name: 192.168.1.x

That worked like a charm. Initially, I wondered if the Named Pipes connection was breaking because the two SQL Servers are on different domains (that have a 2 way trust) but I had no problems connecting to a SQL Server 2000 machine over a truested domain -- no alias needed. SQL Server 2000's Query Analyser required no additional tweaking to connect to SQL 2005. This may be due to the fact that TCP/IP was listed as the first network protocol to attempt.

12 Comments   -
  • Comment by Samir Vaidya | June 13, 2006 @ 4:54 pm

    You can additionally change the database compatibility level so that it is usable by SQL 2005 Management Studio by issuing the following command:

    --Changes the database compatibility level to SQL 2000
    EXEC sp_dbcmptlevel 'My65Database, '80';
    GO

  • Comment by Chrissy | June 13, 2006 @ 5:29 pm

    awesome tip, thanks Samir!

  • Comment by Pete | June 29, 2006 @ 6:57 pm

    Just upgrading from 2000 to 2005 and this tip was very helpful. But, I think I painted myself in a corner.

    I have a 6.5 compatible database that is offline... I can't detach it, and can't use the above EXEC command b/c the database is offline (around and around we go).

    In hindsight, I should have made all my database ONLINE and then updated the compatibility level and all would have been good.

    Thanks,
    Pete

  • Comment by Greg | April 6, 2007 @ 2:38 am

    worked great!! thanks for the tip!!

  • Comment by reGotcha | April 30, 2007 @ 7:25 am

    Great tip for changing db compatibility using a sp command

    --- Reports the current compatibility level of 'MyDBName'
    EXEC sp_dbcmptlevel 'MyDBName';
    GO

    --- Changes it to SQL 2000 - 80
    EXEC sp_dbcmptlevel 'MyDBName', '80';
    GO

    --- Changes it to SQL 2005 - 90
    EXEC sp_dbcmptlevel 'MyDBName', '90';
    GO

  • Comment by David | July 25, 2007 @ 4:48 pm

    Samir , THANKS A LOT, i was stuck with this and didn´t know what to do.

    You're a real saint

  • Comment by Gilligan | December 14, 2007 @ 11:26 am

    This fixed me right up as well. thank you very much!

  • Comment by Oliveira | April 10, 2008 @ 1:23 am

    Hi, i read this article and i want to ask your help, if possible.

    I'm in one near situation... I'm in one company that have one database in SQL server 6.5 too and they want to pass it to sql server 2005.
    At this moment i'm trying to replicate it in my owne pc, but i can't, i'm making ino backup of the original database and try to use it in my owne pc for after try to update widtout the risk of damage data.

    My problem is exactly that... i install sql s 6.5 widhout problems but wen i try to put the backups that i made doesn't alow me... Can u help me?

    Thanks for the attencion.

  • Comment by Bruce Sharp | April 10, 2008 @ 3:57 pm

    Oh, sure, I'm two years late coming to the party, but I feel compelled to chime in and say: Chrissy, Samir, Regotcha... you ROCK. Thanks for a very helpful post!

  • Comment by Adrian Harmer | June 11, 2008 @ 2:52 am

    I agree with Bruce. This is exactly what I wanted and has saved me hours. Thanks a lot.

  • Comment by Cynthia Paulas | June 24, 2008 @ 10:22 am

    Thank you very much. It was just too easy.

  • Comment by Gorgorlou | August 13, 2008 @ 6:15 am

    Thanks Samir and Regotcha,

    Your posts are fantastic. My problem is fixed.

RSS feed for comments on this post. TrackBack URL

Leave your comment