Check if SQL Server version is 2005 or lower

,

This script shows a simple way to check if the current version of SQL Server is 2005 or lower.

It exploits the new server property called 'BuildClrVersion' which is not defined on SQL Server 2000 (and lower) but has just been introduced with 2005.

It may be useful to condition the execution of a piece of code which is required only for an older or the new version.

if SERVERPROPERTY ('BuildClrVersion') is null
begin
    -- some 2000 specific code
  print '2000'
end
else
begin
    -- some 2005 specific code
  print '2005'
end



----------------------------------------------------------
--
-- practical example:
--

-- to allow xp_cmdshell execution which is disabled by default on SQL 2005 but not on 2000
if SERVERPROPERTY ('BuildClrVersion') is not null
begin
  -- To allow advanced options to be changed.
  EXEC sp_configure 'show advanced options', 1

  -- To update the currently configured value for advanced options.
  RECONFIGURE

  -- To enable the feature.
  EXEC sp_configure 'xp_cmdshell', 1

  -- To update the currently configured value for this feature.
  RECONFIGURE
end

Rate

Share

Share

Rate