1

I have a SQL Server DB that is in a SQL Server 2008 Express instance that will not let me change the compatibility level beyond SQL Server 2005 (It was originally created in 2005) either through the GUI or T-SQL. If you view the options in the GUI (Right Click on DB, Select Properties, Select "Options" and then look at the "Compatibility level:", it shows options for only past versions 7(70), 2000 (80), and 2005 (90).

This is in production and I cannot simply drop and recreate it as it is constantly in use. When I backed up and restored it to another SQL Server instance (2014) all compatibility levels were available, but that's not an option without taking down all VM's that are tied to it. If all else fails, yes that is an option, but one that brings no joy.

When I run :

     ALTER DATABASE [VIM_VCDB] SET  COMPATIBILITY_LEVEL =  100

I get the following:

     Msg 102, Level 15, State 1, Line 1
     Incorrect syntax near '100'.

This is true for all system DB's on this box also... How to I raise the compatibility level for this? Is it a bad install of the SQL Server 2008 Express?

closed as off-topic by Shanky, Philᵀᴹ, Tom V, RLF, Paul White Aug 12 '16 at 19:16

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Shanky, Philᵀᴹ, Tom V, RLF, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I just created a new DB and it also only allows for the 3 compatibility levels: 7,2000, and 2005... – Dan B Aug 11 '16 at 20:46
  • 1
    What is SELECT @@VERSION? – Martin Smith Aug 11 '16 at 20:48
  • @MartinSmith - Thanks... Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1) How do I correct/Update?? – Dan B Aug 11 '16 at 20:55
  • 1
    So that isn't SQL Server 2008 Express. You need to either install 2008 from the installation media or connect to the right instance if you did already install it. – Martin Smith Aug 11 '16 at 20:58
3

You have SQL Server 2005 installed and not 2008. You'll need to install 2008 or newer and migrate your databases over to the new instance.

  • Trust but verify... I was given some errant info... Thanks! – Dan B Aug 11 '16 at 20:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.