How to determine SQL server version number for Compact edition 4 up to full sql server 2008?

Lot's of people have asked and been responded to about how to determine the version of the sql server using things like @@VERSION, or SERVERPROPERTY('productversion') but none of these work with sql server compact edition 4.

Is there some universally supported method to determine which sql server edition and version is in use through a sql query or ado.net code that works for compact edition all the way to full sql server?

I want to determine which exact edition / version of SQL server is in use so I know what type of paging query to issue from my code. Sql CE 4 uses a new format for paging queries same as sql server 2011 (denali) and 2005 and 2008 have their own method that is unsupported in CE 4开发者_如何学运维.

I think the answer is that it's impossible but I just want to be sure I didn't overlook something.

I don't really work with SQL Server anymore but here is my attempt at this little problem.

For version 4 of compact edition the following should give you the version and build.

var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion;

And the following should give you the assembly version

var version = typeof(System.Data.SqlServerCe.SqlCeConnection).Assembly.GetName().Version;

Take a look at this blog post. It has a link to download a utility that detects which version of SQL Compact edition you're running. It also has a link to the source code for the utility which may be of interest to you.

You can use PowerShell , in versions of Windows 7 or newer , it comes pre- installed by default. Use the following command lines :

[System.Reflection.Assembly]::LoadFrom("SQLCeAssembly.dll").GetName().Version

Outputs this:

Major  Minor  Build  Revision
-----  -----  -----  --------
4      0      0      0

run this

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

See details here