Problem w/temporary tables in Stored Procedure accessed by SSRS

Question

Problem w/temporary tables in Stored Procedure accessed by SSRS

Tess

Right there with Babe

Points: 744
More actions
July 22, 2004 at 11:01 am

#112092

Hi, I have a stored procedure that I am using to produce the resultset that I want Reporting Services to display. Reporting Services accesses the stored procedure w/o problem, however, I use temporary tables to build interim results that a cursor within the stored procedure uses. The Reporting Services Designer complains that my temporary table "#Results" is not a database object ( duh , 'course not ). If I write this information to actual tables in the database, I don't have the problem. This temporary table data is not necessary to keep outside the stored procedure. Is this a Reporting Services problem or a Visual Studio 2003 .NET problem? Any suggestions as to how I can get this to work?
Thanks in advance.

"All your database are belong to me"

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 1

I use stored procedures along with Temp tables and get the same error. When I set up the data set, it would run the stored procedure with no problem. When I moved over to the design window, I get that error. I ignored the error, I then added the output fields to the fields toolbar, (one at a time) gave them the same name as the output field name would be in a result set. I then drug the field to the report and ran it with no errors. I have used several Stored procedures like this and have not had any issues. I know it's a pain to have to add the fields to the fields tool bar, but at least it allows me to use them. If it was a real table the collection of fields would have been created automatically for you and I thinkthis was were the error was coming from.

Hope this helps

Tess Right there with Babe Points: 744 More actions · Answer 2

Thanks for the workaround. I would hope that particular quirk would be handled better in the next version. In spite of a few quirkinesses, I'm really impressed with the Reporting Services product. Beats Crystal everyway but loose.

"All your database are belong to me"

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 3

I have been doing quite a bit with it and have been evalutating the differences between RS and Crystal. I really am liking RS especially since Crystal Prices are going out the roof.

Tess Right there with Babe Points: 744 More actions · Answer 4

In addition to the fact that it takes less time to spin up on the Reporting Services, I trust the Database Driver for SQL to release connections (which I've been having no end to problems with Crystal), more of our people are familiar with the Visual Studio environment, etc, etc. Makes it more cost effective and I think the reports simply display better and behave better, personal opinion, of course.

"All your database are belong to me"

Anthony Scott SSC-Addicted Points: 408 More actions · Answer 5

Newbie here!!

I am currently evaluating SSRS for our company use as opposed to CR, and am very impressd by what I have seen thus far, and will be demonstrating it to the IT folks sometime in the next week or so.

I have also had the same problems using SProcs as datasets, and was interested in the answer provided. One thing though, I have only used SSRS for a few days, and am not quite sure when you mentioned about adding the fields by hand in the window. I take it this is the Design window (middle tab)? where the dataset drop down and corresponding fields appear to the left and then can be dragged onto the report itself. Can I ask you to explain this 'adding fields' in a little more detail.

Sorry if I'm asking an obvious question, but you seem to have the answer already.

Thanks

Tony

________________________________________________

We passed upon the stair - and I was that man who sold the world

psullivan SSChasing Mays Points: 604 More actions · Answer 6

I have that problem with #temp type tables, however on stored procedures using temp tables like so (declare @temp table(name varchar(30), phone char(10)) i haven't had the same problem.

remember #temp is going away in favor of the the above..

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

psullivan (9/14/2004)
remember #temp is going away in favor of the the above..

Heh... I know it's an old post but 10 years have gone by and that hasn't come close to being true. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anthony Scott SSC-Addicted Points: 408 More actions · Answer 8

.. ahh, well they didn't say when 😀

________________________________________________

We passed upon the stair - and I was that man who sold the world

MMartin1 One Orange Chip Points: 27799 More actions · Answer 9

I wonder if they were seeing the issue of a stored procedure returning one of many temp tables/record sets (created within the procedure), depending an an argument passed. If I pass it 'a' I get back columns a,b,c. If I pass it 'b' I get columns a,b,c,d,e from another temp table. When SSRS creates the data set for this procedure call (what columns to expect) it can't , it gets confused. 😀

----------------------------------------------------

aaron mertes SSC Enthusiast Points: 155 More actions · Answer 10

Just try running it again and it will work, you'll receive that message the first time you try to execute the report but I've found that if you try again it will work w/o issue. Otherwise, another option is to replace the temp table with a table variable.