SQL Server Reporting Services Tips links, questions, complaints, and other thoughts

Lovely crashes

I was experiencing some crashes yesterday that were driving me crazy. Any call to a custom assembly from reporting services would quickly crash devenv.exe and the reporting services service (rshost), with errors like these:

rshost!rshost!2d4!12/15/2011-15:41:53:: e ERROR: Generating a dump and exiting the process due to fatal runtime error.

and

Faulting application name: devenv.exe, version: 9.0.30729.1, time stamp: 0x488f2b50
Faulting module name: unknown, version: 0.0.0.0, time stamp: 0x00000000
Exception code: 0xc00000fd
Fault offset: 0x0e91568b
Faulting process id: 0x14bc
Faulting application start time: 0x01ccbb01f553d8c9
Faulting application path: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
Faulting module path: unknown
Report Id: a4ad4e69-26f5-11e1-a150-001c42714af2

A lookup of the exception ID referred to a stack overflow. I noticed when I moved the code from the custom assembly into the report custom code area, it would no longer crash. It was crazy.

The reason for the crashes? Simply put: a recursion bug! While researching it I even saw another thread where someone had the same exception, caused by recursion, and I thought to myself, "No, that's not it in this case." But a recent code refactoring had added a bug and... BAM.

But here's the kicker.

For whatever reason, an assembly called by rshost won't catch any StackOverflowExceptions. So even if you wrap it in a try/catch block, where you'd typically catch this in your own .NET application, it doesn't work the same way in Reporting Services. Which is a real shame... it shouldn't just crash the service.

(By the way, this is on RS 2008 SP2).

Lesson learned.

System.Security.Permissions.ReflectionPermission

I saw a report today causing a warning in BIDS, that it was denied access to the System.Security.Permissions.ReflectionPermission.

It turns out, in an expression I'd done something like this:
=(BlahBlah).Value

So, pretty simply, you can trigger it by accidentally treating something into an Object by putting a .Value after a parenthesis. It's in the wrong place

Error 7308 and 7303

While creating a linked server between two SQL Server 2008 X64 instances today, I received this error:

OLE DB provider 'SQLNCLI10' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

Looking in the server's ODBC list, the SQLNCLI10 driver wasn't listed as installed. I downloaded the SQL Server 2008 Feature Pack (April 2009) and installed it which added the entry.

Then I received Error 7303:

Cannot initialize the data source object of OLE DB provider

...

What it really meant was that after adding an ODBC driver you should restart the SQL Server service. After that, everything was hunky dory.

Parallels / VirtualBox using lots of CPU during idle

I recently set up a Parallels virtual machine running SQL Server and noticed lots of CPU usage even during idle. I found that people running VirtualBox had similar problems especially in conjunction with SQL Server.

It's caused by SQL Server (2008+ and maybe some other versions with various service packs) constantly polling an interrupt for high-resolution timing, instead of RDTSC (a less intensive CPU instruction that's less accurate especially across multiple CPU cores).

This is a pain in resource-constrainted virtualised environments (and in my case, a laptop/notebook), especially when the precise timing isn't required. A great solution is to go into Services and add a command-line parameter to the SQL Server service:

-T 8038

This sets Trace Flag 8038 which appears to switch back from interrupt timing to RDSTC. You can then restart the SQL Server service and do:

DBCC TRACESTATUS

This will give you a response with the 8038 number if it is turned on properly. This has dropped my idle-time CPU usage from ~25% to ~5%.

Tool to reformat (pretty print) all the SQL code inside an RDL

I use a great tool called SQL Pretty Printer (the Desktop edition) which has a command line interface to format SQL. I decided to whip up a tool in Python to go through and reformat just the SQL portions inside CommandText tags. It's pretty straight forward and could likely be modified easily to work with other 3rd party pretty printers.

# Requires Python 2.7 and PyWin32 http://sourceforge.net/projects/pywin32/
 
import os, shutil, string, subprocess, sys, tempfile, win32con, win32file
 
# This must be updated for the local machine
#
ppexe = r"C:\Program Files\Gudu Software\SQL Pretty Printer\sqlpp_cmd.exe"
ppconfig = r"C:\Users\xxx\AppData\Roaming\gudusoft\sqlpp\layoutTemplates\default.ini"
 
html_escape_table = {
    "&": "&",
    ">": ">",
    "<": "&lt;",
    }
 
def escape(text):
    return "".join(html_escape_table.get(c,c) for c in text)
 
def unescape(s):
    s = s.replace("&lt;", "<")
    s = s.replace("&gt;", ">")
    s = s.replace("&amp;", "&")
    return s
 
# Go to the right solution directory
solutiondir = None
if (len(sys.argv) <> 2):
    while (solutiondir == None):
        solutiondir = raw_input("Solution directory: ")
else:
    solutiondir = sys.argv[1]
 
solutiondir = os.path.join(os.getcwd(), solutiondir)
 
# Get all the RDL files inside
ext = ".rdl"
for root, dirs, files in os.walk(solutiondir): 
    for i in files: 
        if i[ - 4:] in ext:
            fullfile = os.path.join(root, i)
 
            ofile = file(fullfile, "r")
            ocontents = ofile.read()
            ncontents = ocontents
            ofile.close()
 
            startquery = "<CommandText>"
            endquery = "</CommandText"
 
            lastoffset = 0
            while (lastoffset <> -1):
                lastoffset = string.find(ncontents, startquery, lastoffset)
                if (lastoffset == -1):
                    break
 
                # Extract just the query portion
                oquery = ncontents[lastoffset + len(startquery):string.find(ncontents, endquery, lastoffset)]
                lastoffset = string.find(ncontents, endquery, lastoffset)
 
                # Write to a temporary file and process
                tfile = tempfile.NamedTemporaryFile(mode = "w+t", delete=False)
                tfile.write(unescape(oquery))
                tfile.close()
 
                ppresult = subprocess.check_output([ppexe, "-mssql", "-sql", "-inplace", "-config=" + ppconfig, "-F", tfile.name], shell=True)
                if (len(ppresult) <> 0):
                    # If any output was done, then there was likely an error
                    print "Seems like there was an error: " + result
                    break
 
                tfile = file(tfile.name, "r+t")
                nquery = escape(tfile.read())
                tfile.close()
                os.unlink(tfile.name)
 
                ncontents = string.replace(ncontents, oquery, nquery, 1)
 
            if (len(ppresult) <> 0 or lastoffset <> -1):
                print "Error processing " + fullfile + ", skipping"
                continue
 
            # Compare old and new file
            if (ocontents <> ncontents):
                # Backup
                shutil.copyfile(fullfile, fullfile[:-3] + "bak")
 
                # Remove any read-only attribute from Fortress
                ofattr = win32file.GetFileAttributes(fullfile)
                if (ofattr & win32con.FILE_ATTRIBUTE_READONLY):
                    nfattr = ofattr & ~win32con.FILE_ATTRIBUTE_READONLY
                    result = win32file.SetFileAttributes(fullfile, nfattr)
 
                # Write new formatted file
                nfile = file(fullfile, "w+t")
                nfile.write(ncontents)
                nfile.close()
 
                # Restore attributes, not currently necessary
                # if (ofattr & win32con.FILE_ATTRIBUTE_READONLY):
                    # result = win32file.SetFileAttributes(fullfile, ofattr)
 
                print "Formatted " + fullfile

reformat_rdl_sql_pretty_print

Enjoy,
Cody

How to fix obscured text when using scrolling tablix headers

I had a problem today when experimenting with FixedRowHeaders on a Tablix (and setting the static row groups FixedData property, of course). The headers would scroll with the rest of the report down the page, but be obscured by text underneath showing through.

The solution was to set the BackgroundColor of those headers to White instead of No Color (which is like a transparency).

Devart dbForge SQL Complete 3: The worst software update in history

I've been using SQL Complete 2 for a few months now, and picked it up for a fraction of the price of alternatives like Red Gate SQL Prompt Pro or SoftTree Tech SQL Assistant. I use it in combination with SQL Pretty Printer Desktop version, because while SQL Complete's Intellisense assistance is great, its code formatting and options are horrific bordering on unusable.

But while I used to recommend it, I have to steer everyone away from buying the latest version (3.0.14). The entire 3.0 series has been marred with issues and is horribly, horribly broken, crashing SSMS multiple times a day and adding up to hours of lost work.

This has to be one of the worst software updates in history. Maybe I'm expecting too much after just two weeks of patch time after release, however the company also removed access to the 2.x series by paying customers, so myself and perhaps other users who spend all day in SSMS writing code, are suffering a terrible burden right now waiting for a proper fix.

Devart has issued multiple updates trying to resolve the issue, and failed each time. So, until I say otherwise, steer clear.

Update: Devart support is still looking into the 3.0.14 issue, and registered users can still download the 2.x version from their site. Here's the steps from support, which I can confirm is working:

To get SQL Complete v2.50 or other build, please do the following:

1. go to http://secure.devart.com/
2. enter valid login and password
3. click on 3.0.14 version link at the "Licenses" page
4. at the "Download" page click on "For a list of previous versions, click here"
5. download the previous build (version) at the "Available previous versions of dbForge SQL Complete"

Barcodes again, and adding DLLs to a running production server

Recently, I had to switch out the custom barcode code I listed in a previous post on a running production server, and I replaced it with a free open-source software library called BarcodeLib (check the license before using, it's CPOL).

It's not that there was anything specifically wrong with the barcode code, but it wasn't very crisp, and after scanning on low-resolution scanners or being faxed, there were problems picking the codes up.

Here's the quick and dirty after getting the binary package from above:

1. For development purposes in BIDS 2008 you need to drop a copy of the DLL into C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies. This can change in other versions.
2. Add a reference in the report properties to the BarcodeLib.DLL (also, keep the existing System.Drawing reference in there, or add one if you're setting it up for the first time).
3. Write some custom code to generate the barcode. I've put mine below, apparently BarcodeLib can do part of the conversion of the image to Byte() itself but it was faster for me to do it than work out how it does it:

   Public Shared Function NewBarcode(ByVal barcodeText As String, ByVal barcodeLibType As BarcodeLib.TYPE) As Byte()
     Dim result As Byte() = Nothing
    
     Try
       Dim image As System.Drawing.Image = New BarcodeLib.Barcode().Encode(barcodeLibType, barcodeText)
       Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream()
       image.Save(ms, System.Drawing.Imaging.ImageFormat.Png)
       result = ms.GetBuffer()
     Catch ex As Exception
     End Try
    
     Return result
   End Function

4. Now in the report, add your image, set the type to Database, mime type to image/png, and the value to =Code.NewBarcode(BarcodeLib.TYPE.Code128, Fields!BarcodeNumber.Value)

Before deploying the report to production, you also need to dump the DLL onto the production server (for SSRS 2008) C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin. You don't need to restart the service or add additional permissions or anything, after you deploy the report it "all just worked".

As an interesting aside, I noticed that after the report had started being used, a copy of the BarcodeLib.DLL appeared in another directory; C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\RSTEmpFiles\reportserver\13de8c64\97d4cdf9\assembly\dl3\c60781f5\c5268962_7247cc01. I've read about this behaviour before in ASP.NET, where DLLs are copied from the main directory into a temp directory for actual use; that way if you ever need to update the DLL there's no write-lock on the original copy, and it can safely be overwritten while active pages continue to use the "copied" version.

Adding fonts to a running production server

Recently I had to try to add fonts to a live running production server.

I'd noticed on my desktop before that I had to reboot to get the fonts to show up in SSRS, but it appears that (at least in Server 2003 and SSRS 2008), you can just install the fonts in Windows, and (assuming they show up immediately somewhere like notepad), just restart the SSRS service to re-enumerate them and use them in live reports.

TL;DR: No reboot necessary to install fonts live! SSRS does take a minute or two to reload though.

SUM on groups and case sensitivity issues

Two issues today.

I've seen a lot of web posts where beginners get an error or warning when doing a SUM on a certain category of data in a dataset, but outside of a group (because SUM doesn't work properly in groups that have subgroups). For example:

=Sum(IIf(Fields!ProductType.Value = "unicorns", Fields!Qty.Value, 0))

[rsAggregateOfMixedDataTypes] The Value expression for the textrun uses an aggregate function on data of varying data types.  Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

And it's caused by the Qty field being a type that is different to the 0; so you should usually just use Nothing instead.

=Sum(IIf(Fields!ProductType.Value = "unicorns", Fields!Qty.Value, Nothing))

But what I really wanted to talk about today was that I came across a case where "unicorns" wasn't working as a criteria, because the case was wrong. But wait, isn't both SQL Server and SSRS by default case insensitive?

Oddly, it appears that SSRS is case insensitive for fields based on nvarchar and varchar strings, but case sensitive for fields that are char or nchar. Weird, and my searches didn't come up with any sources that could confirm that was the case... it just seems to be what I've come across today.