Let me preface this by stating that I am a T-SQL/Report Developer and admit that I am sorely lacking in DBA skills.
I have a SQL 2005 Server with all databases on it set to compatibility level 80 (SQL 2000). I want to change the compatibility level on one of my databases to 90 (SQL 2005) so that I can use its increased T-SQL capabilities. (I inherited the server with a new job)
Initially I ran this:
ALTER DATABASE [DPM_PED] SET compatibility_level = 90;
Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near '='.
So I went with old trusty:
sp_dbcmptlevel DPM_PED, 90
Which gave me:
Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92 Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.
I've searched the internet and this forum for this issue, but obviously I fail at search engine querying also. Any help would be appreciated.
If relevant, Microsoft SQL Server Management Studio 9.00.5000.00
asked Jun 06, 2011 at 12:32 PM in Default
I think the answer lies in the error message
Seems like you are connected to a SQL 2000 database engine. No matter what version of SSMS you are running, the max compatibility will be restricted by the server.
What do you get when you execute
The database typically has to be in single user mode.
Then you can run
Then put the database back in multi user mode.