The art of simplicity

After upgrading our SQL Server Reporting Services (SSRS) environment, we noticed some isuses: reports were slow, users were complaining, and we had no idea where to start. The good news is that SSRS has been quietly collecting detailed execution data the whole time — and a set of built-in views makes it surprisingly easy to query. This post walks through the ExecutionLog views, what they contain, and how to turn that data into actionable performance insights. What are the ExecutionLog views? SSRS logs every report execution to the ReportServer database. Three views expose this data at different levels of detail: ExecutionLog — A simple view covering the basics: report name, user, start time, and duration. Good for quick lookups. ExecutionLog2 — Adds AdditionalInfo , an XML column with richer metadata such as estimated row counts and data source connection details. ExecutionLog3 — The most complete view. Breaks execution time into three distinct phases — TimeData...

During an Azure DevOps Server (TFS) migration, one of the steps I have to execute is the backup and restore of the Reporting Services databases. After reconfiguring the Reporting Services on the new server, I always have to fix the following error message: The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported) This is caused by a combination of 2 elements: By restoring the database on a new server, the new server is added to the list of reporting services instances which makes Reporting Services think this is a scaled out deployment Most organisations are using a SQL Server Standard Edition for Azure DevOps Server. This edition does not support ‘Scale-Out' deployments’. My solution to fix this problem was always the same: https://bartwullems.blogspot.com/2013/02/sql-server-reporting-services-feature.html Unfortunately during my last upgrade I noticed that the Scale-Out Deployment tab wasn’t even ...

On one of my SQL Server Reporting Services reports, I had to use some custom logic. Instead of embedding the code directly into the report, I decided to create a separate assembly which makes it easier to test and debug this code. After importing the assembly in my Visual Studio 2017 Reporting Services project, I couldn’t load the Report Designer preview window anymore. Instead I got the following error message: An error occured during local report processing. The definition of the report ‘/samplereport’ is invalid. Error while loading code module: ‘Sample.Barcode, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly ‘Sample.Barcode, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. The system cannot find the file specified. To fix this I had to copy the DLL to the following location: C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSRS ...

After opening an ‘old’ Reporting Services project, we got the following exception message when we tried to build the report: Custom parameter layout was detected in the report. SQL Server 2014 Reporting Services and earlier do not support custom parameter layout. Either reduce the error level to a value less than 3 or change the TargetServerVersion to a version that supports custom parameter layout. To fix this error, you have to go to the Report properties: Right click on your Reporting Services Project , and choose Properties . Change the TargetServerVersion to SQL Server 2016 or later

After upgrading to SQL Server 2014, we encountered the following issue while configuring the Reporting Services: When we tried to restore the Reporting Services Encryption key, it always failed. Inside the error logs we found the following information: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is ‘163’. The expected version is ‘162’. (rsInvalidReportServerDatabase). A quick search on the web brought us to the following post: https://blogs.msdn.microsoft.com/sql_server_team/upgrading-to-sql-2014-report-server-version-mismatch/ The problem can happen when you upgrade from a SQL 2012 SP2 server with CU5 or later, the database will have a higher version number than what SQL 2014 expects. Luckily there was already a solution out there. I asked the server team to upgrade to SQL 2014 CU7 after which the issue was gone…

After upgrading to SQL Server 2016 I noticed that our Reporting Services website remained offline and returned a 503 Service Unavailable: A fix was already available as part of Cumulative Update 1 for SQL Server 2016: https://support.microsoft.com/en-us/help/3164674 After installing it, our Reporting Services website was back online…

A colleague contacted me to investigate a problem some of his customers had when opening an RDLC(local Reporting Services report) inside a ReportViewer control. Instead of a nice looking report, they got the following error message: An unexpected error occurred in Report Processing. Could not load file or assembly 'Microsoft.SqlServer.Types, Version=11.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) Strange! We had no clue why this error appeared. I don’t see a reason why this DLL is needed. Anyway we solved it by downloading the assembly through NuGet( https://www.nuget.org/packages/Microsoft.SqlServer.Types/ ) and including it in the installation package. If anyone has a clue why this DLL is required, feel free to share! Remark: Even stranger was that we hadn’t this problem with a previous release.

Microsoft released a free ebook about Power BI : Get started quickly with Microsoft Power BI! Experts Alberto Ferrari and Marco Russo will help you bring your data to life, transforming your company’s data into rich visuals for you to collect and organize, allowing you to focus on what matters most to you. Stay in the know, spot trends as they happen, and push your business to new limits. This free ebook introduces Microsoft Power BI basics through a practical, scenario-based guided tour of the tool, showing you how to build analytical solutions using Power BI. Read the ebook to get an overview of Power BI, or dig deeper and follow along on your PC using the book’s examples. Download PDF: https://aka.ms/IntroPowBI/StndPDF       More information: https://blogs.msdn.microsoft.com/microsoft_press/2016/06/16/free-ebook-introducing-microsoft-power-bi/

Just a quick post so I don’t forget how to get the last time the cube was processed from SQL Server Analysis services. Execute the following query from inside SQL Server Management Studio: SELECT LAST_DATA_UPDATE FROM $system.mdschema_cubes That’s it!

Moving the TFS warehouse to a different server is not that hard and can be done with a few steps: Log in on the TFS server and open the TFS Administration console Go to the Reporting tab Click on Edit and click OK to stop all running jobs. Change the server name and instance to your new SQL server on the Warehouse and Analysis Services tab Click on Test Connection . It should succeed and display a message saying that the database does not exist but will be created. Click on Start Jobs .

Last year , I had a TF400069 error when installing Team Foundation Server 2013. This year, while installing Team Foundation Server 2015 for a customer, the error reappeared. Did I learned nothing?   In this case, the situation was a little bit different than before. I no longer installed the Reporting Services component on the TFS application tier. As a consequence, the SQL Client Tools Connectivity libraries were missing on the application tier. These libraries are required to let TFS talk successfully to the data warehouse. To solve it, load the SQL Server installation media on your application tier(!) , start the installation wizard and enable the Client Tools Connectivity .

After deploying some reports to our reporting services environment, the first time I try to load a report, it always failed with the following error message: An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'dsNorthwind'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server As the error suggested I could get more information if I browse to the report server locally. So I logged into the report server and ran the report again. This time I got a timeout error telling me that it took too long to connect to the database. Similar information could be found in the log files located at: %programfiles%\Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles\ I was able to fix the issue by updating the timeout value inside the shared datasource.

After doing a clean install of my pc I also wanted to install the BIDS(=Business Intelligence Designer Studio) to start some Microsoft Report Services development. But I couldn’t find a version for Visual Studio 2012? So where did it go and how should I develop SSIS, SSRS and SSAS stuff using VS 2012? I discovered that with the 2012 release of SQL Server, the BIDS plugin for Visual Studio was renamed to SSDT, SQL Server Data Tools. SSDT is available for both Visual Studio 2010 and 2012. You no longer need the SQL Server installation media to install it but instead you can just download it from the Microsoft Download Center : You can use this version of the Data Tools to develop applications for both SQL 2008, SQL 2008 R2 and SQL 2012(and of course SQL Azure). After installing it, some new templates are added to your Visual Studio:

Last week, our users send us a bug report mentioning that they cannot print a reporting services report from the web browser. We discovered that printing from the ASP.NET Report Viewer control requires a separate ActiveX to be installed. If you don’t have this ActiveX control installed and the users don’t have the rights to install the ActiveX control themselves, they end up with the following error message: “Unable to load client print control” The browser will ask you to install the required ActiveX control to solve this problem: Remark: Because the printing is using an ActiveX control, you can only print Reporting Services reports from Internet Explorer. If you open the ASP.NET reportviewer in FireFox or Chrome, the print button will not be available.

When restoring a SQL Server Reporting Services database to a new server, the service didn’t want to start. Instead I got the following error message: “The feature: "Scale-out deployment" is not supported in this edition of Reporting Services.” When I was checking the report server configuration manager, I found out that in the Scale Out SSRS deployment both the new server and old server were listed. To clean up the scale out deployment configuration and remove the old server I had to do the following: Open a command prompt. Browse to the following path C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\ List the report servers found in the database type using the following command: RSKeyMgmt –l A list of servers with their GUID is shown. Note the GUID of the server that you want to remove. Remove the instance by using the following command: RSKeyMgmt -r GUIDoftheSSRSserverTobeRemoved Say yes if you are asked for...

Last week a colleague asked me to have a look at a Reporting Services problem. The reporting server returned the following error message when she tried to upload a report: Error : There was an Exception running the extension specified in the config file –> maximum request length exceeded. The problem started to occur after she added a large image to the report. By doing this the report size became too big and the report server started to complain that we are sending more data than it is configured to accept. Fixing this is similar to every other web application, do this by changing the web.config. This config file can be found on the following path: C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportServer Open the config file and find the following line: < httpRuntime executionTimeout = "9000" /> Add a maxRequestLength attribute in there to fix the problem: < httpRuntime executionTimeout = "90...

Microsoft released the Reporting Services Migration tool . A tool that migrates reports and other artifacts from one report server to another report server. It can also be used as a backup and restore tool for Reporting Services. The tool is available both as a command line tool, a GUI and a powershell script. Current features and limitations You can use RSMigrationTool or RSMigrationUI to backup your native report server. To restore or migrate the native server, run the Migration.ps1 from a PowerShell console. Source and target server must be SQL Server Reporting Services 2008 R2 or later. Source server Source server must be native mode using Windows authentication. (MS is working on support for SharePoint integrated mode and other authentication schemes.) Permissions and roles from source server are not backed up o Configuration in source server is not backed up. Reports and other artifacts in deeply nested subfolders may not be backed u...

For an application we are using SQL Server Reporting Services. However we had issues with one report. In this report, we have 2 parameters, e.g. OrderId & CustomerId. The value for CustomerId is calculated by executing a first query using the OrderId parameter. This is something that can be done easily in reporting services. However after configuring the parameters, compilation failed with the following error message: [rsInvalidReportParameterDependency] The report parameter ‘CustomerId’ has a DefaultValue or a ValidValue that depends on the report parameter “OrderId”. Forward dependencies are not valid.   Build complete -- 1 errors, 0 warnings It sounds like a parameter can not depend on another parameter but why did they provided this functionality? Obviously there must be something else that we did wrong. After trying almost everything you could imagine, we finally discovered that the order of the parameters are wrong. In the parameters list of your SQL S...

At a customer, we had some problems with the TFS Datawarehouse cube no longer being processed. When looking at the logs, I noticed the following error message: <LastRun QueueTimeUtc=" 2011-11-10T13:15:59.497Z " ExecutionStartTimeUtc=" 2011-11-10T13:16:00.517Z " EndTimeUtc=" 2011-11-10T13:18:00.637Z " Result=" Blocked "> <ResultMessage> [Version Control Warehouse Sync]: ---> MakeDataChanges() result=DataChangesPending. ---> TF221033: Job failed to acquire a lock using lock mode Shared, resource DataSync: [TFSDB].[Tfs_Warehouse] and timeout 30. </ResultMessage> </LastRun> Some useful links to solve this problem: Monitoring the TFS Data Warehouse – FAQ TFS2010: Warehouse and Job Service Administrator Reports Our solution: Go to the warehouse control service, which in our case is at: http://tfs:8080/tfs/TeamFoundation/Administration/v3.0/warehousecontrolservice.asmx From there click...

On May 26th, Ordina Belgium(the company I work for) is organizing a free technology event. Ordina’s technology specialists will give 21 30 minutes sessions , spread over 5 tracks. The event is free for everyone to join, registration is required. You can register here: www.ordina.be/differentiate . We will have the following sessions for you to attend: Agenda: Stand out from the crowd with Microsoft technology on May 26, 6 pm 30-minute sessions – the choice is yours .Net Visual Studio LightSwitch Building a Windows Phone 7 application: lessons learned C#5 Async SharePoint Automated scripted deployment of SharePoint SharePoint 2010: architectural overview Developing against SharePoint 2010 lists SQL Where .Net meets SQL Server: SQL CLR Extending to multilingual reporting Using geo data within Report Builder 3.0 Application Lifecycle Management Automate your deployment cycle with TFS Build 2010 and Workflow...