Problem
As report designers working with SSDT-BI and SSRS, what are some tidbits that will help solve common issues which occur after upgrading to SQL Server 2014? What should I be sure to check and update after upgrading?
Solution
A passion for creating reports requires using all the tools available to create those reports. As with any tool, there are always work arounds, hard to find properties, and enhancements to help get your job done. I have come up with a few items that do not have enough detail for a single tip; however combining these items into a single tip allows for the solutions to be conveyed.
In this tip, we will discuss:
- a common error that occurs when attempting to preview a SSRS 2014 report in SQL Server Data Tools-BI (SSDT-BI) for Visual Studio 2013
- what properties need to be checked for a project after upgrading
- how to use the BIDS helper tool to find unused datasets
In order to show examples from each of the scenarios, we will use the AdventureWorks database. As of the writing of this tip, a SQL Server 2014 version of the AdventureWorks sample database was not available; thus, I just upgraded the SQL 2012 version. Once you download and install the SQL Server database, we will subsequently use SQL Server Data Tools (SSDT-BI) for Visual Studio 2013 to review the above items.
Common Error when attempting to Preview an SSRS report in SSDT
So you upgraded to SQL Server 2014 and installed all the SSDT-BI tools (see my tip on the SQL 2014 SSDT for Business Intelligence). You open up a previous project that has a simple report and decide to do a quick preview of the newly upgraded report by clicking the preview tab. You watch as a command prompt window pops up (that did not happen in the previous versions) and then…. uhhh-oohhh; the error message shown below appears. </p
A quick summation of the error message:
“There was no endpoint listening at net.pipe://localhost/PreviewProcessngService11108/ReportProcessing that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerExcelption, if present, for more details. The pipe endpoint ‘net.pipe://localhost/PreviewProcessing11108/ReportProcessing’ could not be found on your local machine,”
This seems quite cryptic. From my research, this error is especially common on Windows 8 and Windows 7 machines running SSDT-BI, but can happen on other operating systems. A quick work around exists that actually allows you to run the report via the Run Option, as illustrated next. This process actually executes the report via SSRS and is started by right mouse clicking on the report in question and selecting Run.
Certainly, a more permanent work around is desired to allow the preview tab to work as expected. After doing a fair amount of research, the fix for this error centers around the Net.Pipe Listener Adapter Service. Specifically, this service needs to be started and also set to run automatically at startup. I have read in several places that this service may be missing on some Windows 8 machines; to solve the missing service issue, .Net 4.5 must be installed and / or updated to get this service to appear.
</p
Once the Net.Pipe Listener Adapter is started, the preview button works splendidly, as shown below. Notice how the command prompt pops up still. This window can be minimized, but do not close it! Otherwise, you will get the same error as when the Net.Pipe Listener Adapter service was not running.
Solution/Project Properties to be checked after upgrading
One of the first areas you should review after completing an upgrade is the project deployment properties. The below screen print shows the properties configuration page that is obtained by right mouse clicking on the solution name and selecting properties.
Most of the fields in the above screen print are self-explanatory, but you certainly want to verify that the TargetServerURL is updated to a new server name and path if needed. Also, the TargetServerVersion should likely be updated to “SQL Server 2008R2 or later”, if this value has not been changed.
One additional item that should be reviewed is the ErrorLevel field. Most folks are not aware that the ErrorLevel can be set to various thresholds, from 0-4, which controls what issues are designated as errors and which as warnings during deployment. Warnings will not halt a build where as an error will stop the build and deployment. The severity of the error levels are as follows:
- 0-Most severe and unavoidable build issues that prevent preview and deployment of reports.
- 1-Severe build issues that change the report layout drastically.
- 2-Less severe build issues that change report layout significantly.
- Level 2 is the default error level, but it may be beneficial to adjust according to your needs.
- 3-Minor build issues that change the report layout in minor ways that might not be noticeable.
- 4-Used only for publishing warnings.
Finally, each of the various configurations need to be setup with the appropriate values. By setting up the various configurations, we can deploy a report to a test environment using the debug configuration and then adjust the configuration to Production when we are ready to push the report to the production environment.
Checking for Unused Datasets via BIDS Helper
BIDS Helper is a wonderful add-on tool that is available to assist with many of your SSDT-BI tasks. Although the SSRS feature list in BIDS Helper is a bit small (note the feature set is much larger for SSAS and SSIS and I am planning on doing a few tips on those feature in the future), using the Dataset Usage Report can assist in speeding up report processing. This report tool provides a list of datasets that are not used within a report; since all datasets are run at the time of processing, leaving old datasets within a report can potentially impact performance negatively. To run the Unused Dataset report, after installing BIDS Helper on the machine running SSDT-BI, you right mouse click on the project and select Unused Report Datasets.
As illustrated in the next screen print, the Unused Dataset reports provides us with the name of the report that contains the dataset and the unused dataset name.
Although running through the Unused Dataset report before an upgrade would likely be the best alternative, running it after the upgrade would still be beneficial. The report itself analyzes the backend XML in each of the RDL report files; before deleting any datasets showing up on the list, you should validate that they are certainly no longer needed. You may have also noticed that a Used Report Datasets report is also available to see where each dataset is used.
Conclusion
In this tip we reviewed some helpful tidbits for using SSDT-BI within SSRS 2014. First, in order to preview reports in SSDT-BI, the Net.Pipe Listener service may not be started after an install, and thus must be started and set to run automatically in order to preview reports without an error. Furthermore, you should not close the SSDT-BI previewer command prompt, as doing so will also result in a similar error. Next, after an upgrade and upon opening a project up for the first time, you should review the project properties to verify each of the configurations are setup correctly, point to the correct server, and are using the correct error level. Finally, using a SSDT-BI add-on called BIDS Helper, provides an add-on feature to SSDT-BI which generates an Unused Dataset report. Removing unused datasets, provides performance enhancements as each dataset runs each time the report is processed.
Next Steps
- MSDN Project Properties- http://msdn.microsoft.com/en-us/library/ms178319.aspx
- MSDN SSDT Blog – http://blogs.msdn.com/b/ssdt/
