The Wiert Corner – irregular stream of stuff

When running different SQL server versions and editions, you often want to know what a certain instance is about.
For that, SQL Server T-SQL provides [WayBack] ServerProperty.

Since I absolutely love [WayBack] sqlcmd, I extended my set of SQL Server batch files with this one showing you some of the [WayBack] ServerProperty values:

[sourcecode language=”text”]
call sql-find-sqlcmd.bat
call sql-find-instance.bat
%sqlcmd% -S %sqlinstance% -E -Q "select cast(serverproperty(‘MachineName’) as varchar(20)) MachineName, cast(serverproperty(‘InstanceName’) as varchar(15)) Instance, cast(serverproperty(‘ProductVersion’) as varchar(12)) ProductVersion, cast(serverproperty(‘ProductLevel’) as varchar(5)) ProductLevel, cast(serverproperty(‘Edition’) as varchar(20)) Edition, cast(serverproperty(‘BuildClrVersion’) as varchar(15)) CLR, cast(serverproperty(‘LicenseType’) as varchar(15)) LicenseType, cast(serverproperty(‘NumLicenses’) as varchar(5)) NumLicenses"
[/sourcecode]

That looks complex, so lets split the actual SQL statement across a couple of lines:

[sourcecode language=”sql”]
select
cast(serverproperty(‘MachineName’) as varchar(20)) MachineName,
cast(serverproperty(‘InstanceName’) as varchar(15)) Instance,
cast(serverproperty(‘ProductVersion’) as varchar(12)) ProductVersion,
cast(serverproperty(‘ProductLevel’) as varchar(5)) ProductLevel,
cast(serverproperty(‘Edition’) as varchar(20)) Edition,
cast(serverproperty(‘BuildClrVersion’) as varchar(15)) CLR,
cast(serverproperty(‘LicenseType’) as varchar(15)) LicenseType,
cast(serverproperty(‘NumLicenses’) as varchar(5)) NumLicenses"
[/sourcecode]

For MSDE, it shows this:

[sourcecode language=”text”]
MachineName Instance ProductVersion ProductLevel Edition CLR LicenseType NumLicenses
——————– ————— ————– ———— ——————– ————— ————— ———–
W2K3-VM STARTEAM 8.00.679 SP2 Desktop Engine NULL DISABLED NULL
[/sourcecode]

For SQL Server 2005 Express, it shows this:

[sourcecode language=”text”]
MachineName Instance ProductVersion ProductLevel Edition CLR LicenseType NumLicenses
——————– ————— ————– ———— ——————– ————— ————— ———–
XP-SQL2005 SQLEXPRESS 9.00.4035.00 SP3 Express Edition v2.0.50727 DISABLED NULL
[/sourcecode]

For SQL Server 2008, it shows this:

[sourcecode language=”text”]
MachineName Instance ProductVersion ProductLevel Edition CLR LicenseType NumLicenses
——————– ————— ————– ———— ——————– ————— ————— ———–
VM-W2K3-SQL2008 NULL 10.0.1600.22 RTM Standard Edition v2.0.50727 DISABLED NULL
[/sourcecode]

Related: [WayBack] How can I tell what edition of SQL Server runs on the machine? – Stack Overflow

–jeroen