1

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.

JohnC
  • 3,938
  • 7
  • 41
  • 48

4 Answers4

0

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
Rafael
  • 966
  • 12
  • 22
0

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.

Jason Towne
  • 8,014
  • 5
  • 54
  • 69
  • Hi Jason, sorry I wasn't specific enough I guess. I need a way to know via a sql query what version of sql server I'm connected to so that my code can issue the appropriate format of paging query. – JohnC Apr 15 '11 at 05:04
  • @JohnC, Looking at the SQL Server Compact Edition Function List (http://technet.microsoft.com/en-us/library/ms174077%28SQL.100%29.aspx) it doesn't look like there is a function to return which version is running via TSQL. – Jason Towne Apr 15 '11 at 15:34
0

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;
Gareth
  • 2,180
  • 5
  • 19
  • 24
  • Thanks Gareth, it's what I ultimately ended up doing. Not how I wanted to do it but it does the job. – JohnC Apr 17 '11 at 23:01
-2

run this 

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

See details here

Ron Harlev
  • 16,227
  • 24
  • 89
  • 132
  • The methods in that link will work for all versions of SQL Server except Compact edition which is one of the versions the OP listed in his question. – Jason Towne Apr 15 '11 at 15:39