Can't use stored procedures containing temp tables

jsandee · December 18th, 2007, 07:30 PM

I'm trying to add a stored procedure dataset to my report that contains temp tables. SRS returns the error message 'Invalid object name [table name]. (Microsoft SQL Server, Error: 208)'

I've also tried creating permanent tables from within the stored procedure with the intent of dropping them at the end but SRS doesn't like that either.

It seems like this would be a common user need, I can't imagine this being a limitation of SRS. Does anyone know a way around this?

I'm using SRS 2005 if that makes a difference.

Thanks.

jsandee · December 20th, 2007, 02:24 PM

SET FMTONLY OFF

Turning off this setting at the beginning of the SP corrects this issue.