Temp Tables in Reporting Services

15 December 2008 13:42

While trying to configure a report subscription in SQL Server Reporting Services earlier today, I ran across a problem I've seen a few times, and a few members of my project team have unsuccessfully attacked.  Having finally found a resolution to the problem, I figured I'd make an entry here so I could find it easily later.

In our subscription query, we need to make use of Temporary tables.  The Wizard for generating the subscription itself doesn't naturally find this very friendly, throwing an error that's only slightly helpful: 

"The dataset cannot be generated.  An error occurred while connecting to a datasource, or the query is not valid for the datasource.  (rsCannotPrepareQuery)  Invalid object name '#MyTempTable'."

After quite a bit of diggin around, I found this post, which described a resolution to the problem.  Basically, by adding "SET FMTONLY OFF" at the beginning of the query will alleviate the issue.  As this wasn't a command I was familiar with, a quick tour of MSDN provided the explanation:  "Returns only metadata to the client. Can be used to test the format of the response without actually running the query." 

So by turning it off, it forces the Wizard to evaluate the query--not the non-existant metadata from a temporary table that doesn't exist yet.

Voice Mail Etiquette

12 December 2008 11:05

I just left a message for a coworker on his voice mail for his mobile phone.  I could barely understand his message, as it sounded as if he recorded it while driving in the car.  It reminded me of how much I find it annoying when I hear something like that.

Voice Mail messages, whether for mobile or the office, one should always do it when there isn't any background noise.  It's just basic professionalism.

Tags:

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2024 Jim Moore