SQL Query Into SSRS

Question

SQL Query Into SSRS

S.Mason

Old Hand

Points: 376
More actions
December 3, 2009 at 7:42 am

#143281

Hi Folks, I wonder if you can help.
I have written a query in SQL management studio which writes data to a temporary table and then loops through each row of the temp table using a WHILE clause retrieving a dataset from several other tables.
It works fine and results are as expected.
My problem arises when i transfer the query into SQL Reporting services, the query appears to run fine but only retrieves the first entry and does not loop through.
As it does retieve the first entry I assume the temp table is being created and populated but something is stopping the WHILE clause. Has anyone else come across this kind of behaviour?
I hope this makes sense.
Thanks
Simon

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

S.Mason (12/3/2009)
Hi Folks, I wonder if you can help.
I have written a query in SQL management studio which writes data to a temporary table and then loops through each row of the temp table using a WHILE clause retrieving a dataset from several other tables.
It works fine and results are as expected.
My problem arises when i transfer the query into SQL Reporting services, the query appears to run fine but only retrieves the first entry and does not loop through.
As it does retieve the first entry I assume the temp table is being created and populated but something is stopping the WHILE clause. Has anyone else come across this kind of behaviour?
I hope this makes sense.
Thanks
Simon

Your problem is the temp table SSRS rejects most temp table code so you may need to create a view instead of the temp table.

Kind regards,
Gift Peddie

matzer SSCrazy Eights Points: 9727 More actions · Answer 2

SSRS expects the DataSet to return a single result set. In your case, it looks likes it takes the first result set and ignores the remainder.

Quote from BOL:

Query string
Type a query if Command type is set to Text. If Command type is set to StoredProcedure or TableDirect, type the name of a stored procedure or table. A stored procedure must return a single result set.

At least it is documented for stored procedures.

Peter

S.Mason Old Hand Points: 376 More actions · Answer 3

Thanks for your input folks, it gives me something to look into.

S