Category Archives: SQL

Dynamic number of fields in a query for SSRS 2005

I’ve encountered a problem using SQL Server Reporting Services 2005 (I am using SSRS from an ASP.NET application). Underlying stored procedure can return different number of fields depending on one of the parameter. I.e. if that parameter is NULL the fields returned are

A B C

if that parameter is “1”, the fields returned are:

A B C D_1

if that parameter is “1,2”, the fields returned are:

A B C D_1 D_2

etc. And I need to be able to use those fields with the report (in particular – I need to group on them).  But report’s dataset has a fixed number of fields based on original query and new dynamic fields are inaccessable.

The solution is to modify report definition. I am already doing it to insert custom groups in the report, and reports dataset is exposed via definition as well.  The definition can be downloaded via GetReportDefinition of SSRS Webservice and serialized into a class  (See Tutorial: Updating Reports Using Classes Generated from the RDL Schema for more details). The DataSet in that definition contains array of fields that can easily be resized in code to accomodite for new fields. This way the fields in the definition will match fields returned by the query and can be accessed within the report.

SQL SELECT: Large Row Size (“cannot sort a row of size …”)

Had a weird error today in my MS SQL Query that had worked for months.

Cannot sort a row of size 8304, which is greater than the allowable maximum of 8094

The only difference – a few text fields the query returned had larger amount of data than usual. What saved my day is ROBUST PLAN hint, when I added OPTION(ROBUST PLAN)  to my ORDER BY clause – the query ran perfectly.  So if you ever experience similar problem (usually it happens when you join several tables with large sets of data)  try this approach.

For detailed explanation of ROBUST PLAN and other hints visit Microsoft Tech Net