Upgrade Power BI Reporting Server (PBIRS) to 2018-08 with error.

[english version below]

Nach dem Release der neuen Version des Power BI Reporting Servers (August 2018) sollte dieser wieder schnellstmöglich auf unserem PBIRS in der Version 3/2018 installiert werden.

Dabei kam es wieder einmal zu einem Problem:

Beim Aufruf einiger seitenbasierten Berichte kommt es zu dieser Fehlermeldung:

Failed to load expression host assembly. Details: Could not load file or assembly ‚Microsoft.ReportingServices.ProcessingObjectModel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91‘ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) (rsErrorLoadingExprHostAssembly)

Die Lösung findet sich in dieser Solution zum Fehler: https://community.powerbi.com/t5/Report-Server/August-release-Failed-to-load-expression-host-assembly/m-p/492782#M7249

Nach dem editieren der beiden dort genannten config-Dateien funktionieren die Berichte wieder einwandfrei.

„C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer\web.config“ sowie
„C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer\bin\ReportingServicesService.exe.config“

PS: wenn Ihr bei Power BI Berichten nur den runden Kreis mit den laufenden Punkten zu sehen bekommt, löscht einfach einmal den lokalen Browser Cache.


[english Version]

After Upgrading our Reporting Server (PBI RS) from version 2018/03 to 2018/08 we got error messages when executing normal page-oriented reports:

Failed to load expression host assembly. Details: Could not load file or assembly ‚Microsoft.ReportingServices.ProcessingObjectModel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91‘ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) (rsErrorLoadingExprHostAssembly)

The solution can be found here in this Power BI Community article:

 https://community.powerbi.com/t5/Report-Server/August-release-Failed-to-load-expression-host-assembly/m-p/492782#M7249

After editing the mentioned config Files, the reports are working normal again.

btw: if you get the circle round and round with Power BI reports then try to flush your local browser cache.

Thanks for reading!
Best regards,
Volker

dbWarden – another change in the sp_helpdistributor with SQL Server 2017 CU7 or 6

Another change in the sp_helpdistributor Stored Procedure in CU7 or CU6 of SQL 2017 requires a change in the dbWarden rpt_HealthReport Stored Procedure.
I’ve upgraded from CU5 to CU7, so one of the last two CU changed the System SP again.

Additional to the both that are described in a previous article there needs to be one more parameter to be added before calling the SP:

dist_listener NVARCHAR(200)

After that the Health Report works like before.

Update: with new Replication there is another error.
One more field is missing in the #PUBINFO Temporary table in the rpt_HealtReport
Add publisher NVARCHAR(128) at the end of the temp table and then call sp_replmonitorhelppublication.

Thanks for reading.

Regards,
Volker

Ressources:

dbWarden SQL Server Monitoring Script with SQL Server 2017

[german version below]

I’m still using the free dbWarden Monitoring Scripts for an easy basis monitoring of our SQL Server Environment. Links to the orignal documentation is at the bottom of this short blog article.

With SQL Server 2017 there is a new change neccessary for the Health Report to run properly. It’s nearly the same point that I described on January for SQL 2012 and above (german only).
The Replication Helper procedure is changed and need aditional parameters.
It is the sp_helpdistributor Store Procedure. dbWarden uses a temporary table to receive the results from the helper SP, defined like this:

	/* Replication Distributor */
	CREATE TABLE #REPLINFO (
		distributor NVARCHAR(128) NULL, 
		[distribution database] NVARCHAR(128) NULL, 
		directory NVARCHAR(500), 
		account NVARCHAR(200), 
		[min distrib retention] INT, 
		[max distrib retention] INT, 
		[history retention] INT,
		[history cleanup agent] NVARCHAR(500),
		[distribution cleanup agent] NVARCHAR(500),
		[rpc server name] NVARCHAR(200),
		[rpc login name] NVARCHAR(200),
		publisher_type NVARCHAR(200),	-- additional Fields in the sp_helpdistributor SP from here on:
			[deletebatchsize_xact]	INT, -- VB:2017-11-09-10:01 Field added
			[deletebatchsize_cmd]	INT -- VB:2017-11-09-10:01 Field added
		)

	INSERT INTO #REPLINFO
	EXEC sp_helpdistributor

The call to the SP (EXEC sp_helpdistributor) at the bottom caused the error:
„Column name or number of supplied values does not match table definition“

The two fields (deletebatchsize_xact and deletebachsize_cmd) needs to be added to the temporary table definition before the insert command can be executed without error.
This all is found in the Stored Procedure rpt_HealthReport which is called by the Agent Job Health Report.

Ressources (below)

Thanks for reading,
Voloker


Ich benutze immer noch die kostenlosen dbWarden Monitoring Scripts für eine einfache Überwachung unserer SQL Server-Umgebung. Links zur Originaldokumentation finden Sie am Ende dieses kurzen Blog-Artikels.

Mit SQL Server 2017 ist eine neue Änderung erforderlich, damit der Health-Bericht ordnungsgemäß ausgeführt werden kann. Es ist fast derselbe Punkt, den ich im Januar für SQL 2012 und höher beschrieben habe.
Die Replication Helper Prozedur sp_helpdistributor wurde geändert und benötigt zusätzliche Parameter.

	/* Replication Distributor */
	CREATE TABLE #REPLINFO (
		distributor NVARCHAR(128) NULL, 
		[distribution database] NVARCHAR(128) NULL, 
		directory NVARCHAR(500), 
		account NVARCHAR(200), 
		[min distrib retention] INT, 
		[max distrib retention] INT, 
		[history retention] INT,
		[history cleanup agent] NVARCHAR(500),
		[distribution cleanup agent] NVARCHAR(500),
		[rpc server name] NVARCHAR(200),
		[rpc login name] NVARCHAR(200),
		publisher_type NVARCHAR(200),	-- additional Fields in the sp_helpdistributor SP from here on:
			[deletebatchsize_xact]	INT, -- VB:2017-11-09-10:01 Field added
			[deletebatchsize_cmd]	INT -- VB:2017-11-09-10:01 Field added
		)

	INSERT INTO #REPLINFO
	EXEC sp_helpdistributor

Der Aufruf der SP (EXEC sp_helpdistributor) verursachte den Fehler:
„Spaltenname oder Anzahl der angegebenen Werte stimmt nicht mit der Tabellendefinition überein“

Die beiden Felder (deletebatchsize_xact und deletebachsize_cmd) müssen der temporären Tabellendefinition hinzugefügt werden, bevor der insert-Befehl ohne Fehler ausgeführt werden kann.
Dies ist in der Store-Prozedur rpt_HealthReport zu finden, die vom Agent-Job-Healt Report aufgerufen wird.

Ressourcen:

Danke fürs Lesen!
Volker