Category: SQL Server Reporting Services

SSRS Matrix – Sum fields in a row group but avoid duplicates

If you want to avoid the duplicates for a row group field for the grand total of a row group. Use builtin MAX or MIN aggregate functions for the field that will be used in the sum function.

Wrong implementation
= SUM(Fields!Amount.Value)

Correct Implementation
= SUM(MAX(Fields!Amount.Value, “NameOfRowGroup”))

 

Fix xmlexception rshostingservice.exe ssrs 2017

If your SSRS does not start and worst unable to completely start with the following exception thrown at event viewer

Application: RSHostingService.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Xml.XmlException
at System.Xml.XmlTextReaderImpl.Throw(System.Exception)
at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
at System.Xml.XmlReader.MoveToContent()
at System.Xml.Linq.XElement.Load(System.Xml.XmlReader, System.Xml.Linq.LoadOptions)
at System.Xml.Linq.XElement.Load(System.String, System.Xml.Linq.LoadOptions)
at Microsoft.BIServer.Configuration.ConfigReader..ctor(System.String)
at Microsoft.BIServer.BIService.ServiceConfig..ctor(System.String)
at Microsoft.BIServer.BIService.BIService.SetNewConfigFilePathIfExists(System.String)
at Microsoft.BIServer.BIService.BIService..ctor()
at Microsoft.BIServer.BIService.Program.Main()

 

Also tried to repair the installation and the process ALWAYS fails.

My solution here was to delete the SSRS folder at the default installation folder of

C:\Program Files\Microsoft SQL Server Reporting Services

After that reinstall the ssrs.

Register the old reportserver database in the report server configuration.

Happy Coding!

SSRS Reportviewer – The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

Our client is complaining about a report viewer issue happening only to a single computer. When the c# winforms application tries to open a form class that happens to have a reportviewer object in it, it hangs the whole application and eventually throws an exception.

There is nothing unusual with the SSRS server since it is hosted on a on-premise SQL Server and I happen to know that it is not heavily used.

The c# is throwing an  exception of
The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

with an inner exception of

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

The stack trace for both respectively,

20180104 04:10:34.062
Computer Name: YOG
CFS Version: 2.0.4.9
The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.
   at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
   at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.GetWebResponse(WebRequest request)
   at Microsoft.Reporting.WinForms.ServerReportSoapProxy.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.SqlServer.ReportingServices2005.Execution.ReportExecutionService.LoadReport(String Report, String HistoryID)
   at Microsoft.SqlServer.ReportingServices2005.Execution.RSExecutionConnection.LoadReport(String Report, String HistoryID)
   at Microsoft.Reporting.WinForms.ServerReport.GetExecutionInfo()
   at Microsoft.Reporting.WinForms.ServerReport.SetParameters(IEnumerable`1 parameters)
   at Accounting.Reports.UI.FormReportPreview.LoadReport()
   at Accounting.UI.FormBase.PreLoadReportComponents()
————————————–
————————————–
20180104 04:10:34.062
Computer Name: YOG
CFS Version: 2.0.4.9
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead)
————————————–

 

Hours of research and trial and error led me to this fix which you only need to set this field upon runtime.

System.Net.ServicePointManager.Expect100Continue = false;

A better code is to add additional settings at your application config. This is better because it turned out to be a surgical strike and I never needed to commit unnecessary code to our codebase. The code was applied only to a specific computer.

Just put the code inside the configuration node like this

<configuration>
  <system.net>
    <settings>
      <servicePointManager expect100Continue="false"/>
    </settings>
  </system.net>
</configuration>

Happy Coding!

SQL Server Express Reporting Services and MYSQL

A freelancer would be having second thoughts of developing an SME database under a SQL Server Standard edition or above.

Looked up for the standard edition’s license price and it could cost thousands of dollars. I think it is around $3,000.00 – $6,000.00.

I would rather use MYSQL.

Still you can keep using some of the important features of SQL Server. Reporting services is available on express license.

I have hours researching on how to still use and connect Reporting Services to MYSQL under the express license. Creating a linked server connection is the only way for you to connect SSRS express to MYSQL.

If you are planning to use the ODBC connection directly from the report then it will not allow it.

SSRS Express only allows SQL Server connections. So again you have to use linked servers.