Archives 2019

Please check before upgrading if you have one of the versions mentioned in the upgrade path.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades?view=sql-server-2017#upgrades-from-earlier-versions-to-

After upgrading the SSRS instance with SCCM reporting role installed, you might end up having the following issue when opening a report.

Failed to load expressions host assembly. Details: Could not load file or assembly ‘SrsResources. Culture=Neutral’ or one of its dependencies. The system cannot find the file specified.

This is usually coming from a mismatch in the configuration file of the reporting server and file path for custom assemblies.

In order to investigate you need to look for the file rsssvpolicy.config , which is located in the SSRS installation folder.

Check in the file if the path to the “srsresources.dll” is the proper one and change-it to the actual one if this is the case.

If you upgrade from SSRS 2008 to SSRS 2016 the following steps can fix the problem.

• Copy “srsresources.dll” from:

Source: <SQL Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\Report Server\Bin

Destination: <SQL Installation Path>\MSRS13.MSSQLSERVER\Reporting Services\Report Server\Bin

• Copy “rssrvpolicy.config” (if it was not copied in the upgrade process) from:

Source: “<SQL Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\Report Server”

Destination: “<SQL Installation Path>\MSRS13.MSSQLSERVER\Reporting Services\Report Server”

• Verify this file by searching for “srsresources.dll” and simply check the path to point to the proper location of the file.

• Copy the entire “SCCMErrorResources” folder from:

Source: “<SQL Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\Report Server\Bin”

Destination: “<SQL Installation Path>\MSRS13.MSSQLSERVER\Reporting Services\Report Server\Bin”.

• If the file does not exist, copy and edit the file “perf-ReportServerrsperfctr.ini”, from:

Source: “<SQL Installation Path>\MSRS10_50.MSSQLSERVER\Reporting Services\Report Server\Bin”

Destination: “<SQL Installation Path>\MSRS13.MSSQLSERVER\Reporting Services\Report Server\Bin”

• Edit the path for “symbolfile” to “MSRS13”.

Please don’t forget this is just a workaround and you can use-it at your own risk.

The supported method is to reinstall reporting services. Take into consideration backing up all the custom reports you have in SSRS.

Adjust the source and destination to match your SSRS path.

A PowerShell sample script is available on the blog section to backup the RDL files from the server.

In case that you are receiving the error code (0x80092004) when installing the August 2019 released patches you need to know that this is a consequence of SHA 2 implementation for patch signing.

The following 2 patches should be installed on the machine in order to complete the installation.

• KB4474419 — SHA-2 code signing support update for Windows Server 2008 R2, Windows 7, and Windows Server 2008: August 13, 2019.
• KB4490628 — Servicing stack update for Windows 7 SP1 and Windows Server 2008 R2 SP1: March 12, 2019, not listed as a dependency.

Sometimes is hard to find the proper table or column in the available views.

The easiest way would be to use simple database query to search in the information schema. For Microsoft reference please check the url below. https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-2017

SELECT DISTINCT table_name,column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ('v_%') AND COLUMN_NAME LIKE '%sourcepath%'
SELECT DISTINCT table_name,column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ('v_%') ORDER BY column_name

I used this query for one of my customers in order to export the environment site system details in a datacenter migration scenario. I think this might be useful for other people as well.

DECLARE @Today AS DATE
SET @Today = GETDATE()

DECLARE @BackInTime AS DATE
SET @BackInTime = DATEADD(DAY, -30, @Today )

SELECT DISTINCT
SYS.ResourceID,
SYS.Name0 'Name',
SYS.AD_Site_Name0 'ADSite',
CS.Model0 Model,
SYS.Full_Domain_Name0,
SUM(ISNULL(RAM.Capacity0,0))/1024 'Memory (GB)',
SUM(D.Size0) / 1024 AS 'Disk Size GB',
CPU.[Number of CPUs] as 'Number of CPUs',
CPU.[Number of Cores per CPU] as 'Cores/CPU',
REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS,
CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) as 'Boot Time',
CONVERT(VARCHAR(26), OS.InstallDate0, 101) as 'Install Date',
CONVERT(VARCHAR(26), WS.LastHWScan, 101) as 'Last HW Scan'
FROM
v_R_System SYS
INNER JOIN (
SELECT
Name0,
MAX(Creation_Date0) AS Creation_Date
FROM
dbo.v_R_System
GROUP BY
Name0
) AS CleanSystem
 ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
LEFT JOIN v_GS_COMPUTER_SYSTEM CS
ON SYS.ResourceID=cs.ResourceID
LEFT JOIN (
SELECT
A.ResourceID,
MAX(A.[InstallDate0]) AS [InstallDate0]
FROM
v_GS_OPERATING_SYSTEM A
GROUP BY
A.ResourceID
) AS X
ON SYS.ResourceID = X.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM OS
ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
ON SYS.ResourceID=ram.ResourceID
LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
ON SYS.ResourceID = U.ResourceID
LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
left join v_RA_System_SystemRoleS AS c ON sys.ResourceID = c.ResourceID
LEFT JOIN v_CH_ClientSummary CH
ON SYS.ResourceID = CH.ResourceID
LEFT JOIN (
SELECT
DISTINCT(CPU.SystemName0) AS [System Name],
CPU.Manufacturer0 AS Manufacturer,
CPU.ResourceID,
CPU.Name0 AS Name,
COUNT(CPU.ResourceID) AS [Number of CPUs],
CPU.NumberOfCores0 AS [Number of Cores per CPU],
CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
FROM [dbo].[v_GS_PROCESSOR] CPU
GROUP BY
CPU.SystemName0,
CPU.Manufacturer0,
CPU.Name0,
CPU.NumberOfCores0,
CPU.NumberOfLogicalProcessors0,
CPU.ResourceID
) CPU
ON CPU.ResourceID = SYS.ResourceID
LEFT JOIN v_UpdateScanStatus US
ON US.ResourceID = SYS.ResourceID
WHERE C.System_Roles0 = 'SMS Site System' AND SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
CH.LastOnline BETWEEN @BackInTime AND GETDATE()
GROUP BY
SYS.Creation_Date0 ,
SYS.Name0 ,
SYS.ResourceID ,
SYS.AD_Site_Name0 ,
SYS.Full_Domain_Name0,
CS.UserName0 ,
REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'),
 REPLACE (OS.CSDVersion0,'Service Pack','SP'),
CS.Manufacturer0 ,
CS.Model0 ,
CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
CONVERT(VARCHAR(26), WS.LastHWScan, 101),
CPU.[Number of CPUs] ,
CPU.[Number of Cores per CPU]
ORDER BY SYS.Name0

The query is getting the latest machine in case of eventual duplicates and only if the machine was online in the last 30 days. Adjust the parameters if you have a different requirement.