SSRS Problem/Stored Procedure - One ID field shows up in my dataset; Where are the other fields?

Question

SSRS Problem/Stored Procedure - One ID field shows up in my dataset; Where are the other fields?

scott-733974

SSC-Addicted

Points: 410
More actions
June 12, 2008 at 10:47 am

#208296

Hi All,
I am having a problem getting my dataset fields from my stored procedure to show up in the SSRS BIDS environment. In my stored procedure, I am creating a temp table to populate with data then I am looping through some data in a sql view via a non-cursor type loop. At the end, I use a Select to show the data and drop the temp table.
I need some help from any of your sql experts.
Thanks in advance for your help.
Scott
P.S. If you need to see the store procedure, I will post it. Let me know.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

This is a common issue that a lot of people have posted about. Search the site a bit. SSRS has trouble with these because the temp table does not exist when you try to check for column definitions. One good way to deal with this is to replace your stored procedure with a stub that does not use a temp table and just returns the definition of the result set:

CREATE PROC MyProc

AS

SELECT CONVERT(INT,NULL) AS Field1, CONVERT(VARCHAR(10)) AS Field2

Then, after you deploy, change the procedure so it has the temp table logic.

scott-733974 SSC-Addicted Points: 410 More actions · Answer 2

I have taken a look around and your are right about others having a similar issue. I did modify the stored procedure to using a regular table but that still did not get the fields into the SSRS BIDS dataset. It is still just showing the single ID field. The stored procedure when run in the BIDS IDE does produce the proper output.

I was hoping the modification to a regular table would have taken care of this problem. Am I missing something?

I will try stubbing out the stored procedure and see what happens.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Make sure you have SET NOCOUNT ON - RS does not like the rowcount output.

scott-733974 SSC-Addicted Points: 410 More actions · Answer 4

I have tried all that has been suggested so far. Non of this has worked yet. The solution is coming. I do have a few SSRS reports that are working just fine with temp tables. Don't know why this report is being sooo stuborn. The only thing I see different is the looping that is happening in this stored procedure. The looping structure I got from an article on the forum. Why would loop structure which insert records and updates values would stop the metadata from passing. I don't understand.

Thanks for listening.

scott-733974 SSC-Addicted Points: 410 More actions · Answer 5

I found an entry on one of the other thread from the guy from Austrialia. Just add the fields manually. This actually worked.

Thanks to the guy from down under for giving me something that worked.

scott-733974 SSC-Addicted Points: 410 More actions · Answer 6

Oh and I didn't mean to sound ungreatful for the other suggestions.

Thanks you too MiKe.

maya potiyevskaya SSCrazy Points: 2091 More actions · Answer 7

i use the table variables in place of temp. tables for the stored procs for SSRS.

also, testing your stored procedure first + making sure it does return all the columns you need helps :-). i run this statement exec myStoredPRoc p1, p2 at the query windows in SQL Server first. also, you sometimes need to refresh the dataset at SSRS if you dont see the columns.