Difference in Versioning on SQL Server 2005 x64

[Steffi1013]

I have a SQL Server 2005 x64 installed and when I run select @@version, my result is: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

But if I right click on any of the system databases, and look at the properties - the databases show the compability level at 8.0.

How can this be? What will I need to do to resolve this?

Thanks

 

[SQLBill]

The most likely cause is that you upgraded SS2000 databases to SS2005 and did not change the compatibility mode. Right click on each of the databases, select Properties, go to Options. Look at the entry for Compatibility Level. It should read "SQL Server 2005 (90)"

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Steffi1013]

This server was built as a SQL Server 2005 x64. I did detach and attach a 2000 database from another server to this one, but that should not have changed the compatibility setting from 9.0 to 8.0.

The only thing that was done was a Great Plains upgrade on this server and I still don't see that this upgrade would change the version.

If I change the compatiblity mode of master from 8.0 to 9.0 on the server, what impact will I see? What about MSDB and TempDB and Model?

Thanks!

 

[SQLBill]

The compatibility mode of MODEL is what sets the compatibility of any new databases when they are created. If model is at 80, there is your problem with new databases. The SS2000 database you attached to SS2005 will default to mode 80. I don't know enough about Great Plains, but I do know that another upgrade I did set the mode to 80.

I don't think setting the system databases to 90 would be an issue - BUT - I have never done it, so I can't say it won't cause problems. Do you have a test server?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Steffi1013]

That is quite interesting that an upgrade changes the mode to 8.0 even though it is a 9.0 server.

I don't have a test server that I can test this... when I ran the original test, I did not have this issue. But when the production upgrade package was created and ran - it changed the mode.

I will be changing the mode back to the 9.0 mode tonight and see if it blows up.

What kind of upgrade did you do that changed the mode?

Thanks!

 

[SQLBill]

Sorry for the late response, I'm just not on this site as much as I used to be.

We have a third-party application that we recently upgraded their database from SS2000 to SS2005. When we did, we did not notice that the compatibility mode stayed at 80 until their scripts using SS2005 commands/syntax failed.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875