Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a script which uses features not present in SQL Server 2000. Specifically, the script creates a stored procedure which uses a data type added in SQL Server 2005.

I need to use conditional logic to ensure that (1) the server version is 2005 or later and (2) the compatibility level of the target database is 2005 or later. Further, this script needs to run without errors on SQL Server 2000, obviously without actually adding the new stored procedure.

I'm successfully parsing SERVERPROPERTY('productversion') to determine the server version. However, everything I can find about accessing the compatibility level references SELECT COMPATIBILITY_LEVEL FROM sys.databases WHERE [name] = ('DBName') for SQL Server 2005+ and selecting from sysdatabases on SQL Server 2000. Unfortunately, neither of those methods work on all four SQL Server versions it needs to run on (2000, 2005, 2008, 2008R2).

Ideas?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

This works for 2000 and above:

select name,cmptlevel from master.dbo.sysdatabases
share|improve this answer
    
This was simple and worked like a champ! Many thanks! –  BluJai Apr 8 '11 at 21:57
add comment

I don't know if there is anything better than the below solution for you.

Do the below and capture the results in a table

    CREATE TABLE #DBCC (
    ParentObject VARCHAR (255)
    , [Object] VARCHAR (255)
    , Field VARCHAR (255)
    , [Value] VARCHAR (255)
) 

INSERT INTO #DBCC
EXEC ('DBCC DBINFO WITH TABLERESULTS')

DECLARE @val INT
SELECT @val = Value FROM #DBCC WHERE field = 'dbi_version'

IF ( @val >= 611 )
BEGIN
    SELECT compatibility_level FROM sys.databases WHERE database_id = DB_ID();
    --Deploy 2005 and above stuff
END
ELSE
BEGIN
    --Don't deploy 2005 and above stuff
END

I did something similar for a different problem though.

http://sankarreddy.com/2010/05/database-internal-version-create-version-and-current-version/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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