Sunday, February 8, 2009

Cant use temp tables in stored procs with Sql Reporting Services 2005 Report Wizard

You receive something like:
Invalid object name '#Temp'

The wizard sets "SET FMTONLY ON" before running your procedure - which tells sql server to run the query without processing the results, and just sent over the data format (ie columns, types)

You can add "SET FMTONLY OFF" to the top of your procedure to bypass the wizard check.
http://msdn.microsoft.com/en-us/library/ms173839.aspx

After you do the wizard, you can remove this change.

No comments:

Post a Comment