calling stored procedure in project

hey all

I am very new to stored procedures and would like some clarification please

I have a stored procedure written by someone else

I generally use SQL Server Business Intelligence Studio.

I am designing a report and have called this sp

exec GetTicketsSnapshot (being the name of sp)

1 window only is populated?

if I run this in sql Server Management Studio it populates 3 windows which is what I would expect to see

can someone explain?

thanks

Jewel,

Do you mean that you have 3 different resultset in the same stored procedure? I'm just trying to get a clearer picture of what your issue is.

Ham

|||

hey Ham

Yes that's exactly what I mean.

But it isn't even giving the full result of the first resultset? either

thanks

|||

hey Ham or someone else

Any clues on what I can do here

I have updated since eg I have in my stored procedure above each resultset @.resultset = '0' (// '1' // '2')

I have made three separate datasets to refer to these - and if I run them I get the three separate results

but in my properties of my datasets - the fields are all the same eg they match resultset1

therefore I error in preview - as fields are not there as such.

cheers

|||

Hi,

ok, first of all, only 1 recordset is supported for the reporting services. If you are relying on the logic on the procedure and will not be able to split up the one procedure into three, you will probably have to do the approach you mentioned with providing something like an indexer and giving back only the relevant information to the client / Reporting services. The reporting services interface cannot populate the information for the procedure as it is not aware of the logic that you built in (with selecting one resultset of the three, so for the "discovery of the expected" resultset, the best thing would be to comment out the relevant parts that you do not want to have in the resultset, querying the information from the reporting services interface (that you will have the fields available in the reporting services intefaces) and then set back the procedure to the generic solution. Unfortunately this is the only approach I know. In a former project I had to deal with dynmamic SQL which led me to write the queries in the procedure (without dynamic SQL) querying the meta data of the resultset and the commenting this out to leave the dynamic SQL solution.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql