SQL server compatibilty level

Question

SQL server compatibilty level

tchaturvedi

SSC Enthusiast

Points: 113
More actions
May 16, 2014 at 3:34 pm

#291483

Hi ,
I got a new PC in which SQL server 2005 and SQL server 2008 were already installed. Now I created a new database for my new website and tried to change it's compatibility mode, then in
database name ->'Database Properties' windows->'Options' tab -> 'Compatibility level' drop down
There is no SQL Server 2008 (100) at all. I need to create new database in SQL 2008 only.
Thanks in advance for help.
Tulika

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

If 2008 (100) is not in the list then you're connected to the SQL 2005 instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

Quick test for the version

USE tempdb;

GO

SELECT @@VERSION;

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Eirikur Eiriksson (5/18/2014)
Quick test for the version
USE tempdb;
GO
SELECT @@VERSION;
😎

Actually, you run that select statement in any database as @@VERSION returns the information regarding the SQL Server instance, not the database it is executed in.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

What Gail said is spot on.

If you want to see it in tsql, here is something that will work.

WITH compatversions AS (

SELECT 65 AS MajorVersion ,'SQL Server 6.5' AS ServerVersion

UNION

SELECT 70,'SQL Server 7.0'

UNION

SELECT 80 , 'SQL Server 2000'

UNION

SELECT 90 , 'SQL Server 2005'

UNION

SELECT 100 , 'SQL Server 2008/R2'

UNION

SELECT 110 , 'SQL Server 2012'

UNION

SELECT 120 , 'SQL Server 2014'

)

SELECT TOP 3 *

FROM compatversions

WHERE MajorVersion <= CONVERT(INT,CAST(@@microsoftversion/ 0x1000000 AS VARCHAR(3)) + '0')

ORDER BY MajorVersion DESC

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tchaturvedi SSC Enthusiast Points: 113 More actions · Answer 5

Thanks you all for reply, but I already know the version of the latest instance i.e. 2005, but I have to use already installed SQL 2008 which I don't see, I think creation of new instance for 2008 was missed during installation. Wanted to know if this can be resolved with out reinstallation of SQL server 2008?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

If there is a SQL 2008 database engine, connect to that and you can set a database's compat level to 100. If there's only a SQL 2005 database engine, then no, you can't set compat mode 100, as SQL 2005's compat level is 90.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tchaturvedi SSC Enthusiast Points: 113 More actions · Answer 7

result is

90SQL Server 2005

80SQL Server 2000

70SQL Server 7.0

Thanks again , I know the sql 2008 is not installed properly

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

You're connected to the SQL 2005 instance. See if there's a 2008 instance installed (check services), if there is, connect to that. If not, install a SQL 2008 instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tchaturvedi SSC Enthusiast Points: 113 More actions · Answer 9

thanks for rely,

I have SQL server 2008 R2 installed in my machine.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

If you have a SQL 2008 R2 instance, connect to that and you'll be able to restore a DB and set it's compat mode to 100.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tchaturvedi SSC Enthusiast Points: 113 More actions · Answer 11

I open SSMS od SQL server 2008 R2 only, I try to open database engine there I find only one instance which is of 2005.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

Check the SQL configuration manager, it'll show you all the services installed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tchaturvedi SSC Enthusiast Points: 113 More actions · Answer 13

there also it shows only one server i.e. that is instance of

SQL server 2005 🙁

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 14

Then when 2008 was installed, a new instance wasn't created. Possibly just the client tools were installed. You'll need to reinstall and install a new SQL instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass