none
SSRS 2008 R2 - Stored Procedure dataset does not show fields RRS feed

  • Question

  • Hi

    I am using SSRS 2008 R2 and I have a dataset that uses Stored Procedure with dynamic SQL and it does not show me the fields and looks like I need to add them as calculated fields. Could you please let me know how?

    Thanks in advance...............



    Thursday, July 5, 2012 5:09 PM

Answers

  • When you define a datasource SSRS calls your procedure with 

    SET FMTONLY ON;

    Basically, this allows it to get the list of fields, without running the entire procedure.

    But this causes problems when we use dynamic sql, and we have to update the field list manually.

    However if you put 

    SET FMTONLY OFF; 

    at the top of your procedure, you effective override what SSRS is doing - the full procedure is executed and the field list is returned just fine.

    This is a handy trick if you are having trouble getting field lists to return when using dynamic sql.

    However, I don't see anything in your code that justifies using dynamic sql. 

    Are there other reasons you have elected to use dynamic SQL ?


    Josh Ash



    • Edited by Josh Ashwood Friday, July 6, 2012 1:46 AM
    • Proposed as answer by Edward Zhu Tuesday, July 17, 2012 6:22 AM
    • Marked as answer by Edward Zhu Thursday, July 26, 2012 9:00 AM
    Friday, July 6, 2012 1:45 AM
  • Hello ione721,

    Based on my experience, the report service did not provide the custom error message feature. However, if you just want to post out the warning message or error message, we can just add a TextBox onto the page and fill the warning text. Set the hidden expression of the textbox. If it does not meet your requirement, then show the error message. If the parameter is valid, then hide the warning message. 

    I hope it is helpful to you. If you have any quesitons about this issue, please let me know.

    Regards,

    Edward


    Edward Zhu

    TechNet Community Support

    Tuesday, July 17, 2012 9:36 AM
  • Hello ione721,

    I want to check whether the stored procedure returns the rows when you create the dataset. The field list will be generated only when the stored procedure returns the rows. If it returns no rows, the fields will not generated, since the designer cannot get the fields information. If possible, please try to use a stored procedure which can return rows for test to see whether the fields can be generated automatically.

    If you have any questions about this issue, please tell us freely.

    Regards,

    Edward


    Edward Zhu

    TechNet Community Support

    • Proposed as answer by Edward Zhu Tuesday, July 24, 2012 2:20 AM
    • Marked as answer by Edward Zhu Thursday, July 26, 2012 9:00 AM
    Thursday, July 19, 2012 7:11 AM

All replies

  • Hi,

    First you execute your sql for the no condition which has output for all the fields after that you can execute your sql for condition



    Hope this will help you !!!
    Sanjeewan

    Thursday, July 5, 2012 5:29 PM
  • Hi Sanjeewan, Thanks very much for your response. Not sure if I understand you right, but, here's what I did. I was using SQL Query developed my report and now I am trying to change my source to a stored procedure whaich has dynamic SQL with the same fields and I am stumped because when I add query fields report runs without bringing data and when I try adding the same fileds as calculated fields throws an error "FieldValueException".

    Regards..........

    Thursday, July 5, 2012 5:40 PM
  • Hi,

    I think you are generating your fields dynamically based on condition , so when you are running your query with some condition these fields are not in output

    So try to execute your stored procedure with no condition so that all filed come in output then your this field Value exception will resolved.

    or

    post your query



    Hope this will help you !!!
    Sanjeewan

    Thursday, July 5, 2012 5:51 PM
  • Report query:

    CREATE

    PROCEDURE dbo.TestProc (@StartDate VARCHAR(15), @EndDate VARCHAR(15), @StartWeek VARCHAR(2), @EndWeek VARCHAR(2), @ID VARCHAR(3), @Area VARCHAR(15), @Year VARCHAR(4))

    AS

    DECLARE

    @SQL VARCHAR(MAX), @CLAUSE VARCHAR(MAX)

    IF

    @StartDate = '' AND @EndDate = ''

    SET

    @CLAUSE = '( Week BETWEEN '+ @StartWeek +' AND '+ @EndWeek +' ) AND ( Year IN ('+ @Year +') )'

    ELSE

    SET

    @CLAUSE = '( Date BETWEEN '''+ @StartDate +''' AND '''+ @EndDate +''' )'

    SELECT

    @SQL = 'SELECT Id

    , User

    , Date

    , Week

    , Year

    , MM

    , Location

    , RandomRate

    , Comments

    , Weight

    , AverageWeight

    , HeaderId

    , ReportCriteria

    , AreaCode

    FROM dbo.ReportSummary

    WHERE '

    + @CLAUSE +'

    AND ( Id IN ('

    + @ID +') )

    AND ( ReportCriteria IN ('''

    + @Area +''') )'

    EXEC

    (@SQL)

    Thursday, July 5, 2012 6:13 PM
  •  

    Hi There

    Thanks for your posting. My first question is @Area and @ID are your multivalued parameter? If that is the case then you cannot use them in Stored procedure like this. Why are you using a dynamic SQl. your logic is not look complex at all, why are you using dynamic SQl. Please ignore me if I misunderstood

    I would recommend you please do not use dynamic SQL in Stored procedure if you do not need to.

    Many Thanks

    Syed Qazafi Anjum

     

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, July 5, 2012 10:43 PM
    Moderator
  • Hi Syed

    Thanks for your response. Yes your guess is right but for the time being just to test I am not setting them to be muti-valued parameters. That's the logic on a high level. How do you want me to re-write the query then?

    Regards............

    Thursday, July 5, 2012 11:37 PM
  • Hi Ione721

    Thanks for your posting. If you are not setting them as multivalued then you can use like this and see if you get any result we will deal multivalued later on if we need to

    Many Thanks

    Syed Qazafi

    CREATE PROCEDURE dbo.TestProc (
    @StartDate VARCHAR(15), 
    @EndDate VARCHAR(15), 
    @StartWeek VARCHAR(2), 
    @EndWeek VARCHAR(2),
    @ID VARCHAR(3), 
    @Area VARCHAR(15), 
    @Year VARCHAR(4)) 
    AS
    IF @StartDate = '' AND @EndDate = '' 
    Begin
    SELECT Id
    	, User
    	, Date
    	, Week
    	, Year
    	, MM
    	, Location
    	, RandomRate
    	, Comments
    	, Weight
    	, AverageWeight
    	, HeaderId
    	, ReportCriteria
    	, AreaCode
    FROM 
    	dbo.ReportSummary
    WHERE
    		Week BETWEEN  @StartWeek AND  @EndWeek
      AND  ( Year IN  (@Year ))
      AND ( Id IN ( @ID ) )
      AND ( ReportCriteria IN (@Area)) 
    End
    Else
    Begin
    	SELECT Id
    	, User
    	, Date
    	, Week
    	, Year
    	, MM
    	, Location
    	, RandomRate
    	, Comments
    	, Weight
    	, AverageWeight
    	, HeaderId
    	, ReportCriteria
    	, AreaCode
    FROM 
    	dbo.ReportSummary
    WHERE
    	 [Date] BETWEEN  @StartDate AND  @EndDate 
      AND ( Id IN ( @ID ) )
      AND ( ReportCriteria IN (@Area)) 
    end

    Thursday, July 5, 2012 11:50 PM
    Moderator
  • When you define a datasource SSRS calls your procedure with 

    SET FMTONLY ON;

    Basically, this allows it to get the list of fields, without running the entire procedure.

    But this causes problems when we use dynamic sql, and we have to update the field list manually.

    However if you put 

    SET FMTONLY OFF; 

    at the top of your procedure, you effective override what SSRS is doing - the full procedure is executed and the field list is returned just fine.

    This is a handy trick if you are having trouble getting field lists to return when using dynamic sql.

    However, I don't see anything in your code that justifies using dynamic sql. 

    Are there other reasons you have elected to use dynamic SQL ?


    Josh Ash



    • Edited by Josh Ashwood Friday, July 6, 2012 1:46 AM
    • Proposed as answer by Edward Zhu Tuesday, July 17, 2012 6:22 AM
    • Marked as answer by Edward Zhu Thursday, July 26, 2012 9:00 AM
    Friday, July 6, 2012 1:45 AM
  • Thanks Syed and Josh, I was using Dynamic SQL just to keep the code short.

    Regards..............

    Friday, July 6, 2012 3:41 AM
  • Is there a way I could raise error in the SSRS Report when users choose all of the parameters. Ideally they choose between Start & End dates or Start & End weeks with the Year. If they try choose everthing we should be able to raise an error. How can I accomplish this?

    Thanks........

    Friday, July 6, 2012 3:51 PM
  • Hello ione721,

    Based on my experience, the report service did not provide the custom error message feature. However, if you just want to post out the warning message or error message, we can just add a TextBox onto the page and fill the warning text. Set the hidden expression of the textbox. If it does not meet your requirement, then show the error message. If the parameter is valid, then hide the warning message. 

    I hope it is helpful to you. If you have any quesitons about this issue, please let me know.

    Regards,

    Edward


    Edward Zhu

    TechNet Community Support

    Tuesday, July 17, 2012 9:36 AM
  • Hi Edward Thanks for your response. I figured that SSRS does not provide custom error messages. I implemented the same which you have explained. Thank you all for your inputs.

    P.S: But I am still not able to make the dataset field list showup with dynamic sql in my stored procedure which sure was not the case in SSRS 2005 as I had my procedures as datasets for reports with dynamic sql and had to do nothing any diferent.

    Regards.........


    • Edited by CSharp Enthusiast Tuesday, July 17, 2012 4:15 PM insufficient information
    Tuesday, July 17, 2012 4:04 PM
  • Hello ione721,

    For the show-up the list, I consider that Josh's answer is helpful and I tried it on my side, and the fields list can be shown properly. I consider that it is an odd scenario on your machine. I hope you tried to use the different method to create the dataset to see whether it can generate the field list. For example, if the filed list is not generated in BIDS, please try it in the report builder. In addition, try this on the other computer to see whether there is the issue only on this machine or on all machines.

    If you have any idea about this issue, please tell us freely.

    Regards,

    Edward


    Edward Zhu

    TechNet Community Support

    Wednesday, July 18, 2012 9:24 AM
  • Thanks all for your response. Well there's a way to write custom messages when no data is returned in SSRS 2008 R2 using tablix ‘No Rows Message’ property. In case anyone wants to show a friendly message when the report does not bring any data back.

    Regards.............

    Wednesday, July 18, 2012 6:48 PM
  • Hello ione721,

    I want to check whether the stored procedure returns the rows when you create the dataset. The field list will be generated only when the stored procedure returns the rows. If it returns no rows, the fields will not generated, since the designer cannot get the fields information. If possible, please try to use a stored procedure which can return rows for test to see whether the fields can be generated automatically.

    If you have any questions about this issue, please tell us freely.

    Regards,

    Edward


    Edward Zhu

    TechNet Community Support

    • Proposed as answer by Edward Zhu Tuesday, July 24, 2012 2:20 AM
    • Marked as answer by Edward Zhu Thursday, July 26, 2012 9:00 AM
    Thursday, July 19, 2012 7:11 AM
  • Hi, I am still not comfortable with the way SSRS 2008 R2 responds when the dataset makes use of dynamic sql. The field list needs to be added manually and in the query designer the stored procedure executes fine, when I try to close the dataset properties window it throws the below timeout error.

    SSRS error: Timeout expired.  The timeout period elapsed prior to complTroll etion of the operation or the server is not responding. The statement has been terminated.

    Thanks........

    Wednesday, September 26, 2012 6:15 PM
  • For Josh's suggestion, I can't get it to work.  Whether it's using SET FMTONLY OFF  on top or follow by setting it back ON at the end as suggested by others.

    For a variety of reasons, I need to dynamic SP

    Suggestions??

    Tuesday, July 5, 2016 6:36 PM