Check the end of life status of your SQL Server Compact Installations
SQL Server Compact (SQL CE) was designed as a lighter version of the full-blown SQL Server installation. However, the product has already been discontinued by Microsoft for a while. As a result, the last version of SQL Server Compact is going end of life soon. In September of 2019 Lansweeper also dropped support for SQL CE as the version used then was also going end of life. If you still have other applications running on a SQL Server Compact instance, it is best to start working on migrating them. We also made a complete and detailed blog regarding all the SQL Server (Compact) versions that go end of life.
To help you with finding servers or computers still running an SQL CE installation, you can run the report below to get an overview and when the specific version will go end of life.
SQL Server Compact EOL Query
Select distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As OS,
tblsoftwareuni.softwareName,
tblSoftware.softwareVersion,
Case
When GetDate() >= subquery1.[EOL Date] Then 'EOL'
When GetDate() < subquery1.[EOL Date] Then 'EOL in ' + Cast(DateDiff(DAY, GetDate(),
subquery1.[EOL Date]) As nvarchar) + ' days'
Else 'Supported'
End As Status,
subquery1.[EOL Date],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() >= subquery1.[EOL Date] Then '#ffadad'
When GetDate() < subquery1.[EOL Date] Then '#d4f4be'
End As backgroundcolor
From tblAssets
Left join (Select tblsoftware.AssetId,
Case
When tblsoftware.softwareVersion Like '3.5%' Then '2018-04-10'
When tblsoftware.softwareVersion Like '4.0%' Then '2021-07-13'
Else '2016-04-12'
End As [EOL Date]
From tblsoftware
inner join tblsoftwareuni on tblsoftware.softID = tblSoftwareUni.SoftID
where tblSoftwareUni.softwareName Like '%SQL Server Compact%') As
subquery1 On subquery1.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner join tblSoftware on tblSoftware.AssetId = tblAssets.AssetID
Inner join tblSoftwareuni on tblSoftwareuni.SoftID = tblSoftware.softID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tblState.Statename = 'Active' and tblSoftwareUni.softwareName Like '%SQL Server Compact%'
Order By tblAssets.Domain,
tblAssets.AssetName
