Is Database Running in SQL Server or SQL Server Express?

Question

Is Database Running in SQL Server or SQL Server Express?

Lester Vincent

Ten Centuries

Points: 1227
More actions
March 17, 2010 at 11:44 pm

#221923

Hi People.
It would be easy, I know, to ask the User to answer this, or to record an entry in Windows Registry, for the front-end's code to read. But that may not always be reliable
I am keen, if possible, to create a SPROC that will return this information automatically as my application is booted up.
The purpose is to enable or disable some forms and/or Form Controls which use features available in full SQL Server but are NOT available in SQL Server Express.
I tried using extended SPROC, "xp_msver" and it returned 20 rows of information, but which did not differentiate between the two versions, except for a numeric code that differed from one to the other.
eg, for SSE, "ProductVersion" returned Character_Value=9.00.4053.00
and for full SQL, "ProductVersion" returned Character_Value=9.00.1406.00
on a separate machine,
for SSE, "ProductVersion" returned Character_Value=9.00.3077.00
That really doesn't help to differentiate between the two versions.
Can anyone please throw some light on this?
Regards,
Lester Vincent
Sydney

Viewing 10 posts - 1 through 9 (of 9 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

select @@version

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 2

Thanks Hendrico. That is spot-on what I needed!! I can easily build a SPROC around it which searches the returned string for "Express Edition".

Your response to my post was amazingly fast and I thank you for that!

Kind regards,

Lester Vincent

Sydney

:-):-):-):-)

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

NP, glad it helped.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

This may be of use as well

SELECT SERVERPROPERTY ('edition'),serverproperty('productversion')

Eliminate the string building.

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

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 5

Thanks Jason. That is spot-on !!

Much appreciated guys.

I have what I needed now.

Lester Vincent

Sydney

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

You're welcome.

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

CirquedeSQLeil (3/18/2010)
This may be of use as well
SELECT SERVERPROPERTY ('edition'),serverproperty('productversion')

Also:

SELECT CONVERT(INTEGER, SERVERPROPERTY('EngineEdition'));

This returns a value of 4 for Express (all variations). The 'edition' version distinguishes between Express and Express with Advanced Services.

The SERVERPROPERTY function returns a SQL_VARIANT, so be sure to CAST or CONVERT it to the correct base data type before use.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 8

Thanks Paul. This is a great response to my question!

I have written a VB function in my front-end app which executes a small SPROC to return "SQL" if the back-end is not SQL Server Express Edition, and "SSE" if it is. That is because (it seems) Database Mail is not available in SSE, therefore some forms and form control buttons must be disabled if the back-end is SSE, but enabled if it is "full" SQL.

What you have suggested probably takes me further than what I need, but it is bound to be of use somewhere else.

You have been very helpful, Paul, and I appreciate your time.

Lester Vincent

Sydney

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Paul White NZ (3/19/2010)
CirquedeSQLeil (3/18/2010)
This may be of use as well
SELECT SERVERPROPERTY ('edition'),serverproperty('productversion')
Also:
SELECT CONVERT(INTEGER, SERVERPROPERTY('EngineEdition'));
This returns a value of 4 for Express (all variations). The 'edition' version distinguishes between Express and Express with Advanced Services.
The SERVERPROPERTY function returns a SQL_VARIANT, so be sure to CAST or CONVERT it to the correct base data type before use.

Thanks Paul for providing us with another option. 😎

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