Report for Unsupported versions of MS SQL - missing SPs

[novasam]

I have not included SQL server 2005, 2012, and 2014 yet, just SQL 2008 and SQL 2008 R2 using the MS lifecycle page. Also SQL server 2000 is a different animal. I found it easier to pick a system I know has sql 2000 and use that to find the others.

Here is the link to the MS lifecycle page with all the SQL versions. I'll try and update this script later today.
http://support2.microsoft.com/lifecycle/search/?sort=PN&alpha=sql

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As SP,
  tblSqlServers.serviceName As Service,
  tblSqlServers.lastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Where ((tblSqlServers.displayVersion = 'SQL Server 2008' And
      tblSqlServers.spLevel < '3') Or (tblSqlServers.displayVersion =
      'SQL Server 2008 R2' And tblSqlServers.spLevel < '2')) And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Edition

[Daniel_B]

Moved this to the report center as you have already created the report.

[novasam]

Here is an updated version with 2005 and sql 2012. Would need to add 2014 a year after SP1 is released. Also will need to update the script as SP are released or expire for previous versions of SQL. For example in July 2015 2012 SP1 will EOL.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblSqlServers.displayVersion As Edition,
  tblSqlServers.skuName As License,
  tblSqlServers.spLevel As SP,
  tblSqlServers.serviceName As Service,
  tblSqlServers.lastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Where ((tblSqlServers.displayVersion = 'SQL Server 2005' And
      tblSqlServers.spLevel < '4') Or (tblSqlServers.displayVersion =
      'SQL Server 2008' And tblSqlServers.spLevel < '3') Or
    (tblSqlServers.displayVersion = 'SQL Server 2008 R2' And
      tblSqlServers.spLevel < '2') Or (tblSqlServers.displayVersion =
      'SQL Server 2012' And tblSqlServers.spLevel < '1')) And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Edition