Field list is empty when calling stored procedure

Question

Field list is empty when calling stored procedure

imarchenko-236376

SSCommitted

Points: 1734
More actions
July 20, 2005 at 4:21 pm

#363238

Hello!
   I am having problems retrieving list of fields when using stored procedure as Dataset Source. I noticed that Reporting Services can retrieve field list if stored procedure is trivial (simple select etc.). In my case, I am insrting into temporary table and after some manipulations return resultset. SQL Trace show that RS tries to execute SET FMTONLY ON <sp_name> SET FMTONLY OFF when reading list of the fiels. This returns empty result for my stored procedure.
   I can not move any further without field list being populated properly. I can try to replace stored procedure with the view etc. but I would really like to know if someone experienced similar problems.
   Any help is greately appreciated,
Igor

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

I think this is a known issue. You can work around it by writing a dumy statement that has field names which populates the list and then swap your stored proc back in. the dummy statement just has to be valid, it doesn't even have to hit a table e.g.

SELECT 'a' AS [My_First_ColName], 1 AS My_Int_Col_Name, 1 AS My_Second_Int_Col_Name

Steve.

Dave Klug SSCertifiable Points: 5811 More actions · Answer 2

Another option is to execute the query from the data tab and then click the refresh button. The field list will then be available.

Dave

Habib Zmerli Mr or Mrs. 500 Points: 559 More actions · Answer 3

And dont forget the

set nocount on

at the start of the PS.

HABIB.

Kindest Regards,

Habib Zmerli (MVP)

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 4

imarchenko-236376

SSCommitted

Points: 1734

July 21, 2005 at 11:26 am

#576167

I add set nocount on to all my procs

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 5

I did execute procedure in the Data tab but field list didn't appear.

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 6

Thanks, Steve. I am creating all fields manually. The problem is that I have to remember not to click on Refresh button next time I edit report because it will cause fields to disappear.

Frank Dutcher SSC Enthusiast Points: 198 More actions · Answer 7

I was looking for a solution to my problem, almost identical to the one posted here. I did everything that was suggested but I'm not able to populate my report. The one difference I have is that when on the data tab I run my SP and I get results back. When I go to the preview tab, I get the following warning:

The data set ‘Gainsharing’ contains a definition for the field ‘id’. This field is missing from the returned result set from the data source.

If I remove the field 'id', I get the same warning except that it warns on the next (now first) field.

Any help would be appreciated.

Frank

imarchenko-236376 SSCommitted Points: 1734 More actions · Answer 8

Frank,

It's been a while. If I recall correctly, I have created fields manually. I was able to design/execute report. One thing to keep in mind is that once you refresh dataset in data tab, manually created fields disappear. This is as much as I can say.

HTH,

Igor

Frank Dutcher SSC Enthusiast Points: 198 More actions · Answer 9

Igor,

I found my problem, maybe yours is related.

One of my fields was listed as Fields!StartTimeOfReport.Value. Within the table where I was using this field I did the following. In the textbox, I deleted the =Fields!StartTimeOfReport.Value expression. I then replaced it with

=First(Fields!StartTimeOfReport.Value, "Gainsharing"). I then went to the preview page and my report populated correctly, other then the textbox that I changed, every row had the First value. I then went back to the design window and changed the textbox expression back to

=Fields!StartTimeOfReport.Value and ran the report and it still worked great.

I know at one time I had a different dataset used for the source in my table. I changed it in the properties page, but I suspect that something in the report did not get changed and it couldn't resolve something. By expressly pointing a table element to the

First(Fields!StartTimeOfReport.Value, "Gainsharing") I reset whatever was hung up.

I hope this is clear and understandable.

Frank