x

Change the Compatibility Level for SQL 2005 database from 80 to 90

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;

Which returned:

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

more ▼

asked Jun 06, 2011 at 12:32 PM in Default

myfirstrodeo gravatar image

myfirstrodeo
53 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I think the answer lies in the error message

Valid values of database compatibility level are 60, 65, 70, or 80.

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

select @@version
more ▼

answered Jun 06, 2011 at 12:44 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

Kev,

That is the answer. It is pointed at a SQL 2000 server even though I don't show one installed on this box. Good Times. That solves that problem. Thank you for shaving me with Occam's Razor.
Jun 06, 2011 at 01:03 PM myfirstrodeo
I looked again at my installed programs on the Windows 2003 Server (SP2) and Microsft SQL Server 2000 insn't on the installed programs list. Is it possible that the compatibility level for the entire server was set to 80 when it was created?
Jun 06, 2011 at 01:20 PM myfirstrodeo
I'd be tempted to ignore the installed programs list and believe what the database engine said it was - run select @@version and no matter what compatibility level, it will tell you what version it is
Jun 06, 2011 at 01:31 PM Kev Riley ♦♦
Thanks, again. I told my boss and he wasn't surprised by this revelation. Since this is a Dev box I have been permitted to wipe it and do a clean install.
Jun 06, 2011 at 02:48 PM myfirstrodeo
(comments are locked)
10|1200 characters needed characters left

The database typically has to be in single user mode.

 ALTER DATABASE AdventureWorks  
 SET SINGLE_USER  
 GO  

Then you can run

  EXEC sp_dbcmptlevel AdventureWorks, 90;
  GO

Then put the database back in multi user mode.

 ALTER DATABASE AdventureWorks    
 SET MULTI_USER  
 GO
more ▼

answered Jun 06, 2011 at 12:40 PM

Tim gravatar image

Tim
36.4k 38 41 139

TRAD,

Thanks for your response. BTW, I really enjoyed your presentation at SQL Saturday #77 on what to do when you inherit a database. I even went through all of your slides and my notes before posting to see if I was missing something.
Jun 06, 2011 at 01:09 PM myfirstrodeo
Awesome. Glad to see you on here. I completely over looked part of your question. I was walking out the door and saw the first part about trying to do ALTER and remembered there was a system stored procedure for changing the level. I am glad @kev riley caught it and was able to get you going. Also glad you get to rebuild that dev box. That is one guaranteed way to know what you are dealing with.
Jun 06, 2011 at 03:12 PM Tim
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x292
x84

asked: Jun 06, 2011 at 12:32 PM

Seen: 4649 times

Last Updated: Jun 06, 2011 at 12:32 PM