views:

304

answers:

5

Hi,

I need a safe (i.e consistent, robust) way of detecting whether or not the sql server I'm accessing is Sql Express. I think I'd prefer to be able to do this from TSQL, since I already have a connection string and all the libraries I need to execute TSQL (this should help avoid issues with whether or not WMI is installed/running, the user has permissions to access the registry etc).

Does anyone know of a way to do this ?

Thanks.

PS: Basically I want to do this so I can monitor my database size against the 4gb limit and take action when I approach it... but if I'm running on a full Sql Server edition then I don't want the code to worry about it since there is no (practical) hard coded limit. I could put in a manual setting in my program, but it would be much nicer if the code just did the right thing automatically, hence the need to know if the server is the 'Express' edition or not.

+4  A: 

This will return 0 if it's not, and something non-zero (126 here) if it is:

select charindex('Express Edition',@@version)

Example in a boolean context:

if (select charindex('Express Edition',@@version)) > 0
    begin
        print 'Express edition!'
    end
else
    begin
        print 'Not Express edition!'
    end
Jeremy Smyth
Thanks everyone for the great answers. This one looks good, as do the two using SERVERPROPERTY.
Yort
+3  A: 

You can use SERVERPROPERTY('Edition') which will return "Express Edition"

AdaTheDev
+3  A: 

Use

SELECT SERVERPROPERTY('EditionID')

or

SELECT SERVERPROPERTY('Edition')

to detect which version of SQLServer is running.

http://msdn.microsoft.com/en-us/library/ms174396.aspx

devio
+1  A: 

There are a number of ways:

EXEC sp_server_info 2

Or

SELECT @@version

Or

SELECT serverproperty('ProductVersion')

You Can Also do this:

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
IF ( @ver = '8' )
   SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
   SELECT 'SQL Server 2005'
ELSE
   SELECT 'Unsupported SQL Server Version'

More info at: http://blog.devstone.com/aaron/default,date,2006-12-15.aspx

Lukasz
A: 

What happens in SQL Server Express if you don't monitor the size, but get to the limit? Are there warnings first? Events in the event log?

If so, then you might do better to allow SQL Server to issue the warnings, and then just listen to them. The same might well apply for a SQL Server Enterprise installation if a disk gets full.

John Saunders
There might be warnings in the log, but in our case we have over 200 PC's at 4-5 customers running this solution, each with it's own Sql Express instance. Most of our customers don't have their own IT departments but outsource speciifc jobs, and so there isn't anyone to monitor the logs even if there are warnings.What DOES happen when you reach the limit is all your insert, update and delete statements that might (even temporarily) increase the data size fail, and that is a critical problem for our system.
Yort
My plan is to provide warnings in advance back to a central email address or reporting system for those who do have someone to monitor this, and if the size should become critical, stop the application and refuse to start until the issue is resolved, to prevent data loss.
Yort
Ok, so you're certain there may not be warnings? Because even if they're in the log, you could write code to monitor the log for those warnings.
John Saunders
Also, which version are you using? 2008?
John Saunders