Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kselvia
Aged Yak Warrior
526 Posts |
 Posted - 2004-06-20 : 02:30:48
|
| While trying to use a stored procedure (exec ore_inventory_v2) with SQL Server Reporting Services I received the error;"Invalid object name '#wso'"I ran profiler and found it was executing; SET FMTONLY ON;execute ore_inventory_v2 SET FMTONLY OFF;I get the same error when running that command in QA. Yes it dynamically creates a temporary table. Another site suggested a fix to making OPENQUERY work with stored procedures that also worked for me. I changed the report to retrieve data like this:SELECT * FROM OPENQUERY(LOCALSERVER,'SET FMTONLY OFF EXEC OSL_DATA..ore_inventory_v2')and it worked, but that's just crazy. Any better suggestions? |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-20 : 03:00:02
|
| I found out it was only the New Report Wizard that caused the problem. After I created the report using the SELECT * FROM OPENQUERY above, I was able to go back to the data source and change it to EXEC ore_inventory_v2. |
 |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-22 : 19:35:39
|
| A note on MS Sql Reporting Services Forum suggested the initial query could be changed to:SET FMTONLY OFF;EXEC procnameI thought it would add this in case someone else with the same problem found this post. |
|
|
|
|
|
|
Stored Procedures in SQL Server Reporting Services