October 24, 2008 at 8:12 am
I am in the process of migrating my SQL-Server database to a new production environment. My current production environment is a sql server 6.5 and I am trying to move it to a 2005 environment. I am currently doing testing in SQL-Server 2005 but I am having some problems. When I migrated the 6.5 databases in the 2005 environment it still shows as 6.5. I found a syntax that changed the compatibility level:
EXEC sp_dbcmptlevel 'MyDBName', '90';
GO
However, from my understanding this syntax is for backward compatibility.
Does anyone know the procedure to take to change from 6.5 to 2005
October 24, 2008 at 8:46 am
It's normal behavior for a database to remain at it'a original compatibility level after being upgraded to SQL 2005. sp_dbcmptlevel is the correct way to change the compatibility level. The syntax is:
EXEC sp_dbcmptlevel 'MyDBName', 90;
with no quotes around the level number.
Greg
October 24, 2008 at 9:39 am
Thanks Greg.
October 27, 2008 at 9:27 am
Greg,
I am trying to upgrade my sqlserver database to a 2005 like I stated earlier, I am working with an application called risk master. What I did was to upgrade my 6.5 to a 2000 version of sqlserver , I notice that when I change the compatiblity level of my database from 6.5 to 2000, (using spsp_dbcmptlevelI loose tables. Would you know if there is anything I need to do after I upgrade to keep all of the content in my table?
October 27, 2008 at 9:43 am
You say you are losing tables when you change compatability levels? Are you losing user tables or system tables?
😎
October 27, 2008 at 10:35 am
You should not be losing any tables, system or user. If some things break, it's because the database reacts differently to keywords, and potentially system views. In 2005/2008 there is no sysusers table. There is a sys.users table and my guess is that with compatability level 65, the system translates calls to sysusers to sys.users.
This is a good reason why you don't write code against system tables.
If you let us know specifically what doesn't seem to work, we can help you. If you move to a new compatibility level, you need to be sure you've gone through code to be sure it will still work.
October 27, 2008 at 12:18 pm
My user tables are still there, but I am missing system tables.
October 27, 2008 at 12:39 pm
Since you were jumping from SQL Server 6.5 to SQL Server 2000, it is possible that some of the system tables from 6.5 were depreciated and then dropped from SQL Server. You may want to do some research on MSDN.
😎
October 27, 2008 at 1:02 pm
Thanks, I am looking it up now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply