John Gallardo's Weblog : SQL Server

Hiding parameter area when viewing reports

jgalla — Tue, 24 Mar 2009 00:19:36 GMT

When rendering reports, oftentimes the application would like to minimize as much of the non-report area of the viewer control as possible. This is easily accomplished when rendering the report through the built-in report viewer control hosted in the ReportServer web site (http://servername/reportserver).

There are two parameters which control the rendering of the viewer control area:

rc:Parameters
rc:Toolbar

rc:Parameters can have 3 values:

On	Displays the parameter area as in the screenshot above.
Off	Completely hides the parameter area.
Collapsed	Renders the parameter area initially minimized, so a user can expand it if they need to view/change the parameters for a report.

Here is an example URL which demonstrates rendering a report with rc:Parameters=Collapsed:

https://servername/ReportServer?%2fSamples%2fSQL+2008+Samples%2fAdventureWorks2008+Sample+Reports%2fEmployee+Sales+Summary+2008&rs:Command=Render&rc:Parameters=Collapsed

The other way of hiding the parameter area is to render the report with rc:Toolbar=false. For many applications though, there are side-effects of this approach which I don’t think are desireable. These are:

It disables the automatic ping-mechanism to keep the user’s report session alive. This can lead to rsExecutionNotFound errors if the user leaves the page inactive for several minutes.
It also removes the navigation (next page, find, etc…) UI.
By default, it will cause every page of the report to be returned to the client. For long reports, this is typically not desireable.

So if you are in a situation where you want to minimize the amount of extraneous UI initially presented to the user, while still allowing flexibility in having the end user change the parameter values, consider using rc:Collapsed=true when rendering the report.

Hiding Rendering Extensions

jgalla — Thu, 05 Feb 2009 21:15:00 GMT

This is documented behavior, but we see lots of questions on it. A good mechanism for preventing users from accidently exporting reports to a format that you don’t want (for example, you might know that the report doesn’t render quite right in a particular format) is to mark the extension as “invisible” in the Report Server config file.

This MSDN documentation explains how to configure a rendering extension:

http://msdn.microsoft.com/en-us/library/ms154516.aspx

The important one here is the Visible attribute:

Visible

A value of false indicates that the rendering extension should not be visible in user interfaces. If the attribute is not included, the default value is true.

By setting this attribute to false for a given extension, it will hide the extension from being visible in the Report Viewer control as well as in the delivery configuration pages.

Scaling out the Viewer Control and rsExecutionNotFound

jgalla — Fri, 08 Aug 2008 19:26:14 GMT

One of the criteria that the report server uses to match the provided SessionID with a stored report is that the SessionID has to be provided by the same user that initially created the session. Usually, this is the case. Someone browses the report in IE, they click around to paginate or expand toggles, and things just work because they are the same user they were when they initially ran the report.

Sometimes though, things go wrong.

One way that this can happen is when you are hosting the ASP.Net Viewer Control in your own application and you are not impersonating the incoming user all the way to the backend report server. This is a totally supported configuration, however there is a little caveat that you have to keep in mind. Since the report server requires that the user names match across session retrievals, you have to ensure that the viewer control is impersonating the same user. Sounds easy, right? Well not so fast if you are using a machine specific account for your application pool which is hosting the viewer control. The specific topology that can get you into trouble is something like the following:

In this scenario you have the following:

Client machines accessing multiple web frontends hosting the report viewer control via a load balancer.
The web frontend machines are using a machine-specific account to communicate with the report server (for example they are using the builtin NETWORK SERVICE account).
You are not impersonating the incoming user in the viewer control when accessing the backend report server.

The sequence of operations that can lead to trouble are:

The initial request from ClientA is routed to MachineA.
The viewer control in MachineA instantiates a session with the report server. Since the $MachineA credentials are sent, the session is associated with this user. Keep in mind at this point the report server actually has no idea that there is some other logical user beyond the web frontend that is actually making the request.
The user views the first page of the report, and navigates to the second page.
The request for the second page is actually routed to MachineB by the load balancer.
The viewer control in MachineB attempts to load the user session, however this fails since the request to the report server is actually from $MachineB user and not the original $MachineA user. The report server generates an rsExecutionNotFound error and returns that, resulting in an error being displayed to the user.

There are a couple of ways to address this problem:

Architect your application such that you can flow credentials from the client all the way to the report server. -- Or --
Ensure that your web frontend nodes can impersonate the same user when accessing the report server regardless of which machine the request is routed to (so use something like domain credentials to access the report server).

Scaling Up: SSRS 2008 vs. SSRS 2005 (spoiler: 2008 wins)

jgalla — Thu, 10 Jul 2008 18:59:39 GMT

The SQL Customer Advisory Team just released a Technical Note comparing SQL Server Reporting Services 2008 vs. 2005 from a scale-up perspective. Its good to see that a lot of the work that we did over this release focusing on performance and scalability (across the board, from the core server/processing infrastructure to specific rendering extensions) has really paid off. You can read the entire article here:

http://sqlcat.com/technicalnotes/archive/2008/07/09/scaling-up-reporting-services-2008-vs-reporting-services-2005-lessons-learned.aspx

Quoting from the summary (emphasis is mine):

Reporting Services 2008 was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer. In stark contrast, Reporting Services 2005 generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer.
Our tests clearly show that the new memory management architecture of the report server enables Reporting Services 2008 to scale very well, particularly on the new four-processor, quad-core processors. With our test workload, Reporting Services 2008 consistently outperformed SQL Server 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput. Furthermore, with these renderers on this hardware platform, Reporting Services dramatically outperformed other hardware platforms regardless of Reporting Services version, responding to 3–5 times the number of requests than when running on either of the other hardware platforms. As a result, we recommend that you scale up to four-processor, quad-core servers for performance and scale out to a two-node deployment for high availability. Thereafter, as demand for more capacity occurs, add more four-processor, quad-core servers.

All those temporary files (RSTempFiles)

jgalla — Mon, 30 Jun 2008 21:04:34 GMT

When you install Reporting Services, we create a few directories:

LogFiles
ReportManager
ReportServer
RSTempFiles

Most of these are fairly self explanatory. LogFiles... well we put our log files in there. ReportManager contains the Report Manager application (what you get when you browse to http://<server>/reports) and ReportServer contains the Report Server application which is the proper 'Report Server' which implements our Web Service and HTTP handler endpoints... it is what you get when you go to http://<server>/reportserver.

So what about that RSTempFiles thing? Well it contains files that are temporary of course!

These temporary files can be broken down into a few categories:

Temporary Report Snapshot files.

These files are only created if you have opted into using temporary file storage (see the WebServiceUseFileShareStorage).
For RS2000/2005, snapshots are more or less completely independent and each is contained in its own directory (identified by a GUID). Each snapshot contains a set of files (internally, they are referred to as "chunks").
For RS2008, snapshots oftentimes contain some shared data. The folder-per-snapshot hierarchy used in RS2000/2005 is replaced by a single directory called "Chunks." Each chunk is a discrete file in this directory.
These files are automatically cleaned up by the Report Server on a time-based interval (same as snapshot data stored in ReportServer and ReportServerTempDB catalog databases).

Output/Intermediate Streams

These files are all created directly within the the RSTempFiles directory. There is no way to differentiate between these two sub-categories of files currently.
Output streams - these streams are generated as output from the renderers. The server spools them to disk if they grow large enough. RS has an output cache that may cause these streams to survive beyond the lifetime of the request so that subsequent accesses can be served directly from the cache.
Intermediate files - these files contain results of intermediate calculations during report processing and rendering. Generally, these contain data which is never returned to the client, but rather holds temporary results in order to relieve memory pressure. These files are cleaned up when the request completes (no caching across requests).

Conversion files

These are stored under a folder named _Conversion.
Our compression format for snapshots changed between RS2000 and RS2005. This folder would contain temporary files supporting the on-demand one-time upgrade of these persisted snapshots. Unless you are upgrading from an RS2000 instance, you should never see this directory or files created.

Shadow Copy Files

This is only for RS2008.
After the CTP6 release of RS2008, we enabled shadow copy for our ASP.Net worker domains under the new hosting model.

Where has John been?

jgalla — Tue, 11 Mar 2008 20:35:17 GMT

Ok, it has been a little while. But I have been pretty busy lately. His name is Christopher.

We have also been pretty busy locking down Katmai to get CTP6 (aka known as the "February CTP") out the door and working bugs. I recommend that people head over towards the "Katmai" forum on MSDN for Reporting Services if you have any questions. There are lots of people there willing to answer questions and otherwise help out.

So what now? I owe a part two post on some of the memory management stuff we are introducing in Reporting Services 2008. I'm getting all of the info put together now, and the post should be up shortly.

SQL Server 2008 (Katmai) and Reporting Services

jgalla — Mon, 04 Jun 2007 23:59:22 GMT

Today the first public release of SQL Server 2008 is shipping. Of course, Reporting Services is still there in the box. You can find general information about the release here:

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395&wa=wsignin1.0

There are also some Katmai specific MSDN forums, and a Katmai Reporting Services MSDN forum.

Diagnosing Reporting Services Issues

jgalla — Thu, 29 Mar 2007 19:36:33 GMT

I was poking around some other SSRS blogs from folks here on the product team, and I found this post from Lukasz about troubleshooting Reporting Services. It is a great overview of some of the guidance we have given in the form of whitepapers, KB artciles, and a bunch of other sources. I highly recommend you give it a read and bookmark it so you can use it is your "playbook" when running into problems.

ReportServerTempDB IO Saturation

jgalla — Wed, 21 Mar 2007 21:09:02 GMT

Reporting Services uses a temporary database for storage of objects which are, well, temporary. For example, report snapshots which are associated with a particular user session as the result of a live execution. Cached report snapshots are also stored here because they begin life as a result of a live report execution. This means that in systems where there are a lot of live report executions happening, we are churning through a lot of data in the ReportServerTempDB. As your system scales out or up, you will undoubtedly experience a problem where you will begin to saturate the IO subsystem hosting the ReportServerTempDB.

Do I have an IO throughput issue?

There are lots of really great articles written about this topic, but in general there are a couple of things which I look at:

Perfmon counters for Physical Disk Usage:
- Avg. Disk Sec/Read
- Avg. Disk Sec/Write
Check SQL Server Wait types
- sys.dm_os_wait_stats
- Look for high counts of wait stats like IO_COMPLETION, ASYNC_IO_COMPLETION, and the PAGELATCH_* class of waits

Ideally, you want to see Avg Disk Sec/Read or Write to be < 10ms. If you start seeing these counters creep over 50ms, then you are probably getting into a situation where the disk is not keeping up with the incoming rate of IO requests and this is causing waits.

So how do I deal with it?

The answer is pretty straightforward. You start adding more disks to handle the IO load on the ReportServerTempDB. Doing this is actually pretty easy. From within Management Studio, find the ReportServerTempDB database in the Object Explorer. Right click and go to properties. From the tabs on the left side of the Database Properties dialog, you will see a "Files" section, go to that guy and start adding files. Of course, you will want to spread the files across multiple disks if you are trying to increase IO performance. Additionally, I would recommend sizing the files to an appropriate value rather than relying on autogrowth since autogrowth can cause performance issues when SQL Server actually has to grow the files. The exact value will be a function of how much data is incoming. An important fact to keep in mind though is that the ReportServerTempDB database should automatically remove "stale" data as user sessions and cached reports expire, so the data shouldn't in general continuously grow.

Once you have created the additional data files, SQL Server will automatically begin to load balance IO operations across the various files, so there shouldn't be anything else you have to do. Nice!

Aggregating rsExecutionNotFound Posts

jgalla — Thu, 01 Feb 2007 20:34:00 GMT

The posts about things dealing with rsExecutionNotFound seem to be what people find most interesting these days. In order to aggregate the posts into one place, I will be adding links that deal with this error here.

Maybe I will add a tag as well...

Careful when using rc:Toolbar=false

jgalla — Thu, 01 Feb 2007 02:47:00 GMT

I'm in the middle of reinstalling Visual Studio, and so I thought I would share an interesting story from a couple of weeks ago. An internal customer (Microsoft-speak for some other team at Microsoft) of Reporting Services was encountering seemingly random rsExecutionNotFound errors when navigating around their application. Of course, things like this are never random and there was a perfectly good explanation. After digging through their application for a little while, and with the help of Fiddler, I stumbled upon the problem. They were rendering the same report in two different contexts, one of them was specifying rc:Toolbar=false, and the other wasn't. Basically, their call to the report server looked something like this in the two places:

http://reports/reportserver/?/myreport&rs:Command=Render&rc:Toolbar=false

http://reports/reportserver/?/myreport&rs:Command=Render

Now, there is actually a subtle little problem with rendering using rc:Toolbar=false against the ReportServer virtual directory. When this happens in a default installation, the Report Server sends a cookie back to IE to identify the session. This cookie is then passed back to the report server to load up the appropriate snapshot, parameter, data source information, etc to view subsequent pages and images from the report. What would happen is the user would navigate to the page where the report being rendered w/rc:Toolbar=false was sitting, which would give IE the cookie. The user would navigate away from the page, and eventually head to the page where rc:Toolbar=false was not specified. However, even in this case if the cookie is there, we try to use it to load the user session, which would fail because the session had timed out in the time between the two requests. The user would be greeted with an "Execution Not Found" error message and would have to close and restart IE to get things working again.

The moral? Don't render against the ReportServer virtual directory using rc:Toolbar=false unless you really know what you are doing, because it is tricky to get right. If you want to render using the ReportServer virutal directory (so you don't have to roll your own Report Viewer Control ASP.Net application) then you should build a stylesheet which removes the toolbar, and use that.

Load Testing Reporting Services

jgalla — Tue, 16 Jan 2007 04:52:21 GMT

This question came across one of our internal discussion aliases, and it wasn't the first time I had seen it... so I thought it might be interesting to other people as well.

Oftentimes, when people configure a load testing tool they set it up so that all of the connections come from the same logical user. However, in Reporting Services we have a feature which prevents an authenticated user from performing a denial-of-service attack against the server. We do this by limiting the number of operations that any given user (based on the user's name) can be performing at any given time. When you hit this limit, you will get an error message like this:

There are currently too many requests in progress for user: "ABC\XYZ". Please wait until the current requests have finished processing before issuing any more.

At this point, there are basically two ways of resolving this issue:

Change your test scripts/application to spread the requests across multiple logical users.
Alter the rsReportServer.config file to extend the number of requests a user can have active. The setting is the <Configuration> section and it is the "MaxActiveReqForOneUser" property. The whole entry is this: <Add Key="MaxActiveReqForOneUser" Value="999999" />.

Note that this setting can also be useful if your users are not talking directly to the server, but are instead going through an ASP.Net application which is always impersonating the same user when accessing SSRS.

Diagnosing rsInternalError

jgalla — Tue, 05 Dec 2006 20:52:02 GMT

You know the one. You try to render a report and you end up hitting the dreaded "An internal catalog exception has occurred" error message. Generally speaking, this is an indication that something very bad happened within the server which is not necessarily due to any particular action you performed, but rather due to some internal check which failed or an exception which we were unable to recover from was thrown. We purposefully do not display the entire error message to prevent information disclosure. Instead, we write some more detailed information into one of our log files.

Tracking down the log file which contains the error can be a little tricky if you have never done it before. I am going to assume that the error was generated as a result of interaction with the SSRS Web server, which handles all interactive (non-subscription) requests. This is what you see when you are using the Report Viewer control in server mode, Report Manager, or talking to the ReportServer virtual directory directly via either SOAP or URL Access.

The log files for the web server are stored in %Program Files%\Microsoft SQL Server\<Instance>\Reporting Services\Log Files. First thing you will want to do is head over there and look for the most recent file that look has a name like "ReportServer__<TimeStamp>". There will be a lot of files which almost look like this (such as ReportServerService__Timestamp) but we aren't interested in those just yet. So crack open the log file which corresponds in time to when the internal catalog exception was thrown, and do a search for the string "InternalCatalogException". Once you have found the exception, you will see a couple of things:

A call stack which shows where the exception occurred.
Any previous exception which was handled and wrapped in our InternalCatalogException.

Here is an example:

w3wp!library!5!12/01/2006-15:36:47:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Could not allocate space for object 'dbo.ChunkData'.'IX_ChunkData' in database 'ReportServerTempDB' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.CancelableSqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.ChunkStorage.CopyImages(Guid fromSnapshotID, Boolean fromIsPermanent, Guid toSnapshotID, Boolean toIsPermanent)
at Microsoft.ReportingServices.Library.ReportSnapshot.CopyImageChunksTo(ReportSnapshotBase target)
at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Boolean isLinkedReport, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
--- End of inner exception stack trace ---

Sometimes, this will be enough information for you to diagnose the situation and resolve it on your own. For example, maybe the database where the RS catalog is being stored has run out of disk space. Other times, things won't be particularly clear. In those cases I recommend either contacting Microsoft Support, or posting your question on the SSRS Forum at MSDN.

A Great Place to Ask a Question

jgalla — Tue, 05 Dec 2006 20:27:23 GMT

Recently I have had a couple of people send questions directly to me via the "Email" tab asking me for some specific one-on-one technical support for Reporting Services. I thank you very much for your interest in our product, but unfortunately I am not the best person to direct specific questions towards. Instead, I recommend you check out the SQL Server Reporting Services MSDN Forum. There are lots of folks there both from the community and within the product group here at Microsoft that will be more than happy to answer your questions on the public forum. The great thing there is that forums are also searchable by others, so your question may help out one of your peers. How cool is that?

That said, if there is a topic which you think would be of general interest to others who are using Reporting Services, or if you just want to say "Hi", then feel free to contact me via the contact page. I can't guarantee that I will cover all topics that people ask, but I will make note of them all and if I feel that I cannot provide a good discussion of the topic, then I will forward it on to one of my colleagues who will.

Transitioning between SOAP and URL Access

jgalla — Sat, 18 Nov 2006 00:59:12 GMT

The SSRS Web Server supports two basic protocols, SOAP and URL Access. The SOAP API is great for managing the catalog and doing some session management within the context of a report session, but is generally not the greatest thing in the world when actually rendering a report. The problem is that since the SOAP API returns a byte[] from the Render() and RenderStream() methods, your client application has to hold the entire contents in memory. In addition, because of how ASP.Net's SOAP serialization works on the server, there is some additional memory overhead when rendering a report through the SOAP API. For this reason, I recommend that you consider using our URL Access API to render reports via a simple HTTP GET request. The problem with this though is that if your report contains many parameters or data source credentials or if you need some other kind of interactivity then using pure URL Access can be a bit cumbersome. A nice solution for this problem is to mix usage of both the SOAP API and URL Access. The key is simply communicating the proper execution id for all of the requests.

For some background on how to use URL Access and the SOAP API please check out these links:

The easiest way to show this is by way of an example. In my scenario I want to render a report to PDF, but I want to specify the data source credentials via the SOAP API.

The first step is to create the session and bind it to a report. This is done via the ReportExecutionService.LoadReport method. We then query the returned ExecutionInfo object to determine which data sources need credentials and provide the credentials. Once that is complete, we are good to go and we can construct the URL to render to the report from, including the output format and the session id which was returned as part of the ExecutionInfo. It is also possible to retrieve the ExecutionId property from the SOAP header on the ReportExecutionService object.

In this example, the report that I rendered turned out to be ~ 1MB in length. Rather than having to allocate that entire byte[] in memory, I am instead able to stream the results from the WebResponse object in 4KB buffers.

The complete code for this example is here:

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Net;
using Example.ReportExecution;

class Program
{
    static void Main(string[] args)
    {
        try
        {
            using (ReportExecutionService re = new ReportExecutionService())
            {                
                re.UseDefaultCredentials = true;
                Console.WriteLine("Loading the report...");
                ExecutionInfo execInfo = re.LoadReport("/ProductCatalog", null);
                if (execInfo.CredentialsRequired)
                {
                    List<DataSourceCredentials> credentials = new List<DataSourceCredentials>();
                    foreach (DataSourcePrompt dsp in execInfo.DataSourcePrompts)
                    {
                        DataSourceCredentials cred = new DataSourceCredentials();
                        cred.DataSourceName = dsp.Name;
                        cred.UserName = "asdf";
                        cred.Password = "mypassword";
                        credentials.Add(cred);
                    }

                    Console.WriteLine("Setting data source credentials...");
                    execInfo = re.SetExecutionCredentials(credentials.ToArray());
                }

                string format = "PDF";
                string requestUri = string.Format(
                    @"http://localhost/reportserver/?{0}&rs:SessionId={1}&rs:Format={2}",
                    execInfo.ReportPath,
                    execInfo.ExecutionID,
                    format
                    );
                WebRequest request = WebRequest.Create(requestUri);
                request.UseDefaultCredentials = true;                

                Console.WriteLine("Request report...");
                using (WebResponse response = request.GetResponse())
                using (Stream readStream = response.GetResponseStream())
                using (FileStream writeStream = new FileStream(@"c:\output.pdf", FileMode.Create))
                {
                    byte[] readBuffer = new byte[4096];
                    int bytesRead = 0;
                    while ((bytesRead = readStream.Read(readBuffer, 0, readBuffer.Length)) != 0)
                    {
                        writeStream.Write(readBuffer, 0, bytesRead);
                    }
                }

                Console.WriteLine("Request finished...");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
    }
}