Is it possible to programmatically distinguish between versions of SQL Server?

Question

Basically, is it possible to identify if some-one hooks up my program to SQL server Compact or Express Edition? I want to be able to restrict different versions of my product to different versions of SQL Server.

Answer 1

Run this SQL statement

SELECT @@VERSION

and it'll give you a ReultSet (one column as a String) with the version like this

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Answer 2

After connection to a database, you can always run the T-Sql:

SELECT SERVERPROPERTY ('edition')

This should give you the different editions

Other useful info may come from:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel')

Answer 3

There's also the SERVERPROPERTY function that can give you things like 'edition':

SELECT SERVERPROPERTY ('edition')

Answer 4

The Microsoft Knowledgebase article KB321185 describes how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level.

It also describes how to identify the specific edition if you are using Microsoft SQL Server 2000 or Microsoft SQL Server 7.0.