Facing problem with SSRS using Temporary tables in stored procedures

Hi,
I am facing a problem using temporary tables in SQL Server stored
procedures when using them in SQL Server Reporting Services.
It gives an error while fetching fields when creating a dataset in
report designer of MS Visual Studio.Net 2005.
Has anybody else faced the same problem?
Regards,
SunnyWhat's the specific error it's producing? You can see this if the procedure
raises errors or you do alot of SELECT...INTOs rather than declaring your
temporary tables.
-T
"sunny" <sunny.mohan@.gmail.com> wrote in message
news:1148742869.635884.186170@.38g2000cwa.googlegroups.com...
> Hi,
> I am facing a problem using temporary tables in SQL Server stored
> procedures when using them in SQL Server Reporting Services.
> It gives an error while fetching fields when creating a dataset in
> report designer of MS Visual Studio.Net 2005.
> Has anybody else faced the same problem?
> Regards,
> Sunny
>|||Temporarily modify your procedure to create a table. Use the temporary procedure to populate your dataset. After SSRS has the metadata from the table, you can drop the table and restore your original procudure.
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||I used temporary tables in my stored procedures just fine without going
through this monkey business. One of the biggest problems is that users do
not change their command type to stored procedure!
If they did so, then it works just fine. I.e. when you create a dataset,
specify that the command type is StoredProcedure instead of the default
which is text. Then just type in the name of the stored procedure.
The other thing is to make sure they ARE running the latest Service Pack for
RS.
=-Chris
"Jeff" <nospam@.developmentnow.com> wrote in message
news:e6b4a988-3741-40f4-8359-487d262ce05c@.developmentnow.com...
> Temporarily modify your procedure to create a table. Use the temporary
> procedure to populate your dataset. After SSRS has the metadata from the
> table, you can drop the table and restore your original procudure.
> From
> http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||I'm having the same problem and the drop-down list for Command Type is greyed out, so I'm unable to change the default command type of text. How can I activate the command type drop-down list
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com