Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Common Issues After Upgrading to SSRS 2014 and the BIDS Helper Tool


By:   |   Last Updated: 2015-01-23   |   Comments (6)   |   Related Tips: > Reporting Services Migration

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. The database is available on Codeplex at http://msftdbprodsamples.codeplex.com/releases/view/55330. 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.

Report Error

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.

Report Run

Report Preview

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.

NetLibrary Start Service

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.

Preview

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.

Project 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.

Adjust configuration

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. BIDS Helper can be downloaded from CodePlex at: http://bidshelper.codeplex.com/. 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.

Run Unsed Dataset Report

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.

UnUsed Dataset Report

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.

UsedDatasets

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


Last Updated: 2015-01-23


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 14, 2016 - 1:38:01 PM - Ravi Back To Top

 

My problem:


i had installed BIDS on my system. i am getting error when i open script task. the error details are " Cannot show the editor for this task" & "value does not fall with in the expected range ( mscorlib)".i was uninstall lot of time. but i am not able to identify the issues.

 

Can you suggest to fix the issues.

 

Ravi

 


Sunday, June 26, 2016 - 10:47:45 PM - Kevin Kueny Back To Top

Scott - I figured out my problem - I was looking at the properties of the solution, and I should have been looking at the properties one level down from that - the project?  I forget what it was called now, but that set of properties had everything I needed.  Thanks!

 


Saturday, June 25, 2016 - 10:32:39 AM - Scott Murray Back To Top

Kevin... see if this link helps:

https://msdn.microsoft.com/en-us/library/ms155802(v=sql.120).aspx

 


Friday, June 24, 2016 - 9:01:34 PM - Kevin Kueny Back To Top

 Hi - I am running SQL 2014 & VS Studio 2015 Community.  I wrote and tested a report, but got an error when attempting to deploy that indicated I need to set the TargetServerVersion property on my solution.  However, when I go to the property page on the solution, there are no properties to edit under Configuration Properties.  Any idea what I might be missing?

 


Wednesday, January 28, 2015 - 10:47:34 AM - Scott Back To Top

guilherme,

The build should "skip' the none used report.  Also you can check on the project properties and StartReport.


Tuesday, January 27, 2015 - 4:48:17 PM - Guilherme Back To Top

Nice tips here, thank you!

I have a question, I'm migrating from using Visual Studio 2008 to Visual Studio 2013 and the solution has too many RDLs.

It happens now that, whenever I wanna preview ONE single RDL it builds ALL RDLs. Is that expected? Is there a option to change so that the preview does not trigger a build of all reports?

I know this might sound not related to your post, but it's one issue we can across when migrating.

Thank you!


Learn more about SQL Server tools