Musings on Data

A blog for data, apis, and all things Power BI by Lukasz Pawlowski.

  • Musings on Data

    How to diagnose issues when running reports in the report server?

    • 7 Comments
    A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsible for – data-driven subscriptions. In looking at their solution, we discussed any number of problems they had encountered. I realized that it can be difficult to find out where to start looking when a problem occurs. This post will hopefully provide you a starting point as you endeavor to fix the issues you run into. Reports can take up - a lot of memory, - a lot of time to execute, - a lot of CPU Generally speaking, it is possible for errors to occur as a result: - Out of Memory - Internal Errors - Rendering errors This begs the question, “How to diagnose issues when running reports in the report...
  • Musings on Data

    Reporting Services HTTP 401 (Unauthorized) - Host Headers require your attention

    • 6 Comments
    In a previous post , I talked about issues with repeated login prompts when trying to access either Report Server or Report Manager using your browser. Recently, during an upgrade of our internal test server we identified a new type of HTTP 401 (Unauthorized) message. This post provides a description of the problem and the solution. Symptoms: When all of the following symptoms apply, it could be this issue: A. When accessing Report Manager or Report Server you use a URL that looks like http(s)://<foo>/reports. The name <foo> is NOT the computer name of the computer on which Report Server and Report Manager are deployed. However, DNS or the machine's hosts or lmhosts files (WINDOWS\system32\drivers\etc) are configured to send requests...
  • Musings on Data

    Per user snapshots? Or multiple snapshots for a report each with different parameters at the same time

    • 3 Comments
    I've received questions in the past about whether the report server supports per user snapshost - that is report snapshots customized with different parameter values for a given user. The answer I have always given is no - you get one snapshot per report and that snapshot always uses the default parameter values. Well, it turns out that some inventive users have come up with a 'work around'; it involves some coding on your part. But here's the sketch: Though RS supports only 1 *execution* snapshot per report, it supports N *history* snapshots per report. Also, you can cause a history snapshot to be created programmatically. So there we have it - "all" you need to do to create a set of history snapshots with different parameter values is...
  • Musings on Data

    SQL Server 2005 SP1 Reporting Services database expected version is 'C.0.8.43' problem

    • 3 Comments
    (Originally I thought this post should be titled: SQL 2005 SP1 Tattered my Report Server Database – What’s up with that?) When you upgrade SQL Server 2005 to SP1 and you are running Reporting Services, you may start to get the following error message: 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 'C.0.8.40'. The expected version is 'C.0.8.43'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) First the solution: Got to Start à All Programs à Microsoft SQL Server 2005 à Configuration Tools à Reporting Services Configuration Connection to your report server on the connection...
  • Musings on Data

    Monitoring Subscription Status (also calling RS SOAP methods from inside reports)

    • 2 Comments
    While posting on the Reporting Services forum, I came across a post by some folks looking to know how to automatically find out that a subscription had failed to run. Right now, the way the RS tools are, there is no way to do this automatically without calling a SOAP method - ListSubscriptions. These folks were DBAs who wanted to monitor RS without needing to build an entire application to do so. The solution they were trying was to access the report server database directly to read values out its tables. They weren't impressed when I told them that MS does not support direct access to the report server database other than for getting data out of the execution log. There are several ways to get around the lack of subscription monitoring...
  • Musings on Data

    How to trigger a subscription in Reporting Services 2000

    • 2 Comments
    RS 2000 doees not support custom events. What this means is you cannot, on the face of it, trigger a subscription on demand. Luckly, we thought of this. The method of doing this is not particularly pretty, but it does work. Eventing in Reporting Services is currently (2000 and 2005) limited to two events: SnapshotCreated and TimedSubscription. The TimedSubscription event is interesting because it can be submitted to the report server using the FireEvent SOAP API. You can use this property to programmatically submit events to Reporting Services. What you need: 1) A shared schedule that will never fire. This is easy – just create a shared schedule that executes once and that starts in the past (e.g. yesterday). Take a note of the schedule...
  • Musings on Data

    SQL Server 2005 Notification Services Components Package RC1 Availability

    • 2 Comments
    As part of this blog, I comment about Notification Services. I wanted to provide you the following information about the availability of a pre-release SQL Server 2005 Notification Services components package. If you have questions about SQL Server 2005 Notification Services, visit the SQL Server Notification Services Forum on MSDN . You can help improve the final version by submitting bugs to the Connect Feedback Center . Take care and good luck, -Lukasz ---------------------- SQL Server 2005 Notification Services Components Package Availability The SQL Server 2005 Notification Services component package made available in the February 2007 release of the Microsoft Feature Pack for SQL Server 2005 is being updated to include Notification Services...
  • Musings on Data

    Seattle Building Permits visualized in Power BI with Socrata OData and data.seattle.gov

    • 2 Comments
    Living in Seattle, it’s quite fun to keep track of what’s happening through data. The Open Data initiative from the City of Seattle is a treasure trove of information that you can explore here: http://data.seattle.gov . This is all power by Socrata , as are many other open data initiatives for Cities and organizations. I wanted to explore that data using Power BI, specifically Power BI designer. In a future post, I’ll show how to monitor insights using the new Power BI Dashboards feature, but that’s for later. I wanted to look at Building Permit data – since there’s a lot of construction happening in and around town. I was able to find the “Building Permits : Current” data set which lists “Building...
  • Musings on Data

    SQL 2008 RTM - Where is Report Builder 2.0?

    • 1 Comments
    We've just released SQL Server 2008 (it feels great by the way!) and there have already been many e-mails asking us where to find Report Builder 2.0. You can find an update on the release plan for Report Builder 2.0 on the main Reporting Services Blog: http://blogs.msdn.com/sqlrsteamblog/archive/2008/08/07/report-builder-2-0-release-plan-update.aspx Take care and good luck, -Lukasz
  • Musings on Data

    New Reporting Services Portal on MSDN

    • 1 Comments
    Just adding a quick note about the new MSDN Home Page for Reporting Services. It has links to many great pieces of content including articles, blogs, Webcasts, and references to online docs. http://msdn2.Microsoft.com/en-us/sql/aa336316.aspx Take care and good luck, -Lukasz
  • Musings on Data

    SQL PASS Community Summit 2008 Presentation Links

    • 1 Comments
    Denny Lee and I presented on Wednesday at the SQL PASS Community Summit 2008 on Building SSRS 2008 Large Scale Solutions. The slide deck is available for conference attendees at from the conference site . However, the format is PDF and during the transformation from PowerPoint to PDF many of the links were lost. I have compiled all the links from the presentation deck below for everyone's reference: SQLCAT.com http://blogs.msdn.com/sqlcat http://blogs.msdn.com/mssqlisv http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx Planning for Scalability and Performance with Reporting Services Upgrading Reporting Services (SQL Books Online) Configuring a Report Server Scale-Out Deployment Building and Deploying Large Scale SQL Server Reporting...
  • Musings on Data

    Support for RS 2000 RDL in RS 2005

    • 1 Comments
    I've started getting questions like the following: Given a report developed on SRS 2000 can that report run or be deployed unaltered on a SRS 2005 install can that report be modified on a SRS 2005 install Given a report developed on SRS 2005 can that report run or be deployed on a SRS 2000 install? Can that report be modified on a SRS 2000 install? An RS 2000 RDL report can be published and run on an RS 2005 server. When you request the RDL from the report server for such a report the original, unmodified rdl is returned. You can use the RS 2000 report designer to publish to both an RS 2000 and an RS 2005 report server. An RS 2005 RDL cannot be published to/edited in the RS 2000 server or tools. When you use...
  • Musings on Data

    SQL PASS 2005 musing and picture!

    • 1 Comments
    SQL PASS was held in Dallas, TX the week before last. At the event I gave 3 talks - one end-to-end RS talk, an upgrade talk on both RS and NS, and an NS breakout drilling a little more into the inner workings of NS. All the presentations went well and were well attended. All this is well and good, but really this article is an excuse to show you how many Microsofties it takes to screw in a light bulb. To give you the scenario - Michael Raheem (left most in the photo) was organizing a live upgrade session during the David Campbell's keynote. To do that, he recruited a set of customers at PASS. That's right - no preparation, just whoever wanted to give it a try and had he mix of components (various features of SQL Server, interesting uses of...
  • Musings on Data

    Populating the Cache before your users pound your report server

    • 1 Comments
    Often people take a look at their report execution times and wonder how to optimize it. They readily see that it is possible to cache a report the first time a user runs it so that subsequent users don't have to incur the initial execution time. Well that still leaves the first person to take the 'pain' of waiting for the report to run. If yours is like many organizations, the 'first person' is actually a race right at 8am to see who clicks the report. Many folks end up waiting through that initial report execution time. Luckily, there is a feature of reporting services that allows you to solve this and prepopulate the cache before your users show up to click the report. Before we get to the solution, a little background. There...
  • Musings on Data

    Upgrading Reporting Services - What about my Report Definitions (RDLs)?

    • 1 Comments
    Some folks have asked me what happens when I upgrade my RS 2000 to an RS 2005 deployment with respect to Report Definitions (RDLs). This is an interesting lifecycle problem that we’ve approached using the following idea: What you publish to the report server is what you retrieve from the report server. Here’s how it works: Say you have an RS 2000 Reporting Services deployment that has a number of working reports in it. You probably have one or more corresponding report projects that you edit in Report Designer in Visual Studio 2003 .Net. When you publish these reports, the RDL is stored in the Report Server Database (not the file system!). The RDL is stored and we create a corresponding compiled version of the report that we also store...
  • Musings on Data

    IE 7 Beta 3 Issue when viewing reports in Reporting Services

    • 1 Comments
    There are a couple of threads on the Reporting Services MSDN Forumn and newsgroup about IE 7 Beta 3 freezing when trying to view Reporting Services reports. Just wanted to put some quick info out for folks who run into this issue: Firstly, we know abou the issue (we too hit it in our internal testing just not in time to catch Beta 3). It only affects IE 7 Beta 3. Beta 2 is fine. Secondly, the IE team is working on a fix to the issue for us. It will show up in later builds of IE 7 as they become released for broader testing. Take care and good luck, -Lukasz
  • Musings on Data

    Getting Started with Reporting Services

    • 1 Comments
    The question sometimes comes up for how to get out of the gate fast when ramping up on Reporting Services. Luckily the User Education team has been doing a fantastic job cranking out tutorials about Reporting Services. This is where I would start: http://msdn2.microsoft.com/en-us/library/ms170246.aspx And then I’d follow with search queries for specific topics on RS. Use MSN Search or Google with the suffix “reporting services site:msdn2.microsoft.com”. Eventually msdn2.microsoft.com will be merged to msdn.microsoft.com, but for now it is ok to include it in your search queries. Take care and good luck, -Lukasz
  • Musings on Data

    Daylight Savings Time issues with Notification Services - update your databases!

    • 1 Comments
    With the coming change to Daylight Savings Time in the United States, you will need to update your NS application databases for NS v2 and NS v2 SP1. For SQL Server 2005 NS, you will need to update your databases for existing applications. SQL Server 2005 SP2 contains a fix to ensure new applications created after applying SP2 do not encounter this problem. Here is the KB article that includes database scripts you can use to update the time zone information affected by Daylight Savings Time changes. http://support.microsoft.com/kb/931815/ SP2 is not available at the time of this post, but it will be prior to the Daylight Savings Time change taking effect. Take care and good luck, -Lukasz
  • Musings on Data

    Scale Testing Reporting Services

    • 1 Comments
    I wanted to provide a quick note on how to scale test Reporting Services. Partially I'm posting this so I don't lose the link :-). Often we are asked how best to scale test Reporting Services and what scale numbers to expect coming from a particular configuration. We always hesitate to promise specific numbers because so much depends on your usage patterns, your reports, the size of your data, and your architecture. To say that N users will always be supported by X hardware is just too much of a simplification. Therefore the guidance we give is based on patterns we have seen in our testing lab. You can see the guidance we gave for SQL Server 2005 Reporting Services here: http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx...
  • Musings on Data

    Keeping your report servers awake (or No more waiting for report server to startup)

    • 1 Comments
    Update 5/3/2010: With time and experience, we learn. So of course an easier way exists to do what this blog post states. Just set the Idle Time-out to zero on the Application Pool configuration. Bill Staples gives the UI snapshot of where to make the change here: http://forums.iis.net/t/1148433.aspx HTH. Reporting Services in SQL 2005 is hosted in IIS. IIS has a performance optimization built-in that you cannot disable (AFAIK), which shuts down an application once there is a period of 20 minutes of inactivity. This is governed by the Idle Timeout IIS Metabase property. You can read about this Idle Timeout here: http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/b39a8ab9-7331-4bad-a89a-0210aadbd4a7.mspx...
  • Musings on Data

    Finding Reporting Services Content faster

    • 1 Comments
    Hi everyone - I've been away from my blog for a little while. However, now again I have some time to start blogging again. I get tons of customer questions over the course of a week. I’d like to share with you how I make my life easier by using Internet Explorer’s built-in search box to help me find things faster. Other browsers provide similar capabilities, so you should be able to get it working there as well. The first issue I'd like to address is an issue that has come up a number of times in a number of ways. I first addressed it here . But since then SQL Server 2008 has made a lot of progress and I find I need to find the latest documentation, quickly. How to Add a SSRS Search Provider to Internet Explorer 7: 1. Find the Search...
  • Musings on Data

    Solving the Reporting Services Login issue in the February CTP of SQL Server 2008

    • 1 Comments
    I’ve seen customers run into authentication issues using the SQL Server 2008 February CTP of Reporting Services (you should all give it a try!). This post provides solutions and a detailed discussion of the underlying causes. The material in this post is applicable to all SSRS deployments, beyond the scope of solving the specific issue. Symptom: A) When you access report server or report manager, you get a login prompt multiple times and eventually you get a blank screen. B) When you try to publish reports/models from Business Intelligence Development studio, you get access denied errors. Solutions: 1) Remove RSWindowsNegotiate and ensure RSWindowsNTLM is specified in the file rsreportserver.config 2) OR, change the report server service account...
  • Musings on Data

    SQL Server 2008 Reporting Services Scalability

    • 1 Comments
    Much has been said up to now about the scalability work the Reporting Services team has done in the SQL 2008 version. However, until now, there were no numbers to back up the architecture discussions. The SQL Server Customer Advisory Team (SQLCAT) just released a Technical Note titled Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned . This is a must read for anyone looking to compare the two products. It also shows that an investment, even now, in SQL 2005 Reporting Services provides a roadmap to higher scalability in the future. Previously, I discussed an approach for Scale Testing Reporting Services that is very similar to the approach used in the Technical Note. Some key notes from the Technical Note: 1) " Reporting...
  • Musings on Data

    IIS Troubleshooting for SQL Server 2005 Reporting Services

    • 1 Comments
    Over the years, we have seen a number of IIS related issues that cause trouble for users who deploy SQL Server 2005 Reporting Services. We have tried to resolve these in the MSDN Forums on a case by case basis. Now we have compiled the common issues with IIS into a single KB Article along with explanation of why these occur and what can be done to resolve them. Kudos to James Wu on the SSRS team for slogging through the issues and compiling them into this document. How to troubleshoot IIS configuration issues in SQL Server 2005 Reporting Services Take care and good luck, -Lukasz
  • Musings on Data

    InternalCatalogException, ASSERT, or NullReferenceException when using a Custom Security Extension

    • 1 Comments
    We have seen multiple customers with issues when customizing the Security Extension Sample ( 2005 , 2008 ) that is available on http://www.codeplex.com/ . This is the sample that demonstrates how to create a Forms Authentication solution with SQL Server Reporting Services. There are two scenarios which are the primary sources of the problems: Scenario 1: “Anonymous” access Many custom security extension authors want to allow ‘anonymous’ access – where users do not need to login, or to allow all users some basic level of permission without needing to login explicitly. In order to do this, they change the sample code’s implementation of AuthenticationExtension.GetUserInfo to return a NULL for out IIdentity userIdentity. However, this approach...
Page 1 of 3 (53 items) 123