none
ssrs 2012

    Question

  • Hello,
    One of the reports in ssrs 2012 is using a stored proc (sp).
    The datasource is calling the sp but it is not retrieving any fields...
    After investigation, I found that the datasource is not populated because the sp is using a function.
    If I remove the function in the sp, the datasource returns fields.
    On another server, ssrs 2008, the above works fine and datasource does indeed return fields. So this seems to be a setting on the SSRS.
    Question:
    How is this solved in SSRS please?
    Friday, January 10, 2014 10:41 AM

All replies

  • Hello ,

      The best way to check whats happening is run the profiler and see the procedure getting executed against the database. Try running the procedure from the backend itself to make sure it returns some data for the passed parameters. 


    Best Regards Sorna

    Friday, January 10, 2014 12:36 PM
  • Unless we understand what function does its difficult to suggest something. Can you post function code?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, January 10, 2014 12:52 PM
  • Unless we understand what function does its difficult to suggest something. Can you post function code?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Hi,
    everything on both sql servers is the same. The only thing is that there may be a setting on ssrs on both servers which may not have the same settings.

    The function does the same thing as the one in the other server which works fine.
    Thanks

    Friday, January 10, 2014 1:15 PM
  • Hi arkiboys,

    According to your description, I have tested a simple report in my SQL Server Reporting Services(SSRS) 2012 environment, while it works well. So I cannot reproduce this issue based on the limited information. In order to trouble shoot this issue more efficiently, I need to clarify some information.

    • Can you try to execute the stored procedure in SQL Server Management Studio (SSMS)? What results can you get? If the stored procedure couldn’t be work well, you can try to refer to the suggestion which is posted by Sorna.
    • In order to reproduce this issue in my own environment, maybe I should know the function which is used in the stored procedure. Can you post the function?
    • Another possibility is that this phenomenon is can be caused by having added some filters in the Dataset in SSRS2012. So you can try to double-check this.


    If there are any misunderstanding, please elaborate the issue for further investigation.

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    Monday, January 13, 2014 7:53 AM
    Moderator
  • Hi arkiboys,

    According to your description, I have tested a simple report in my SQL Server Reporting Services(SSRS) 2012 environment, while it works well. So I cannot reproduce this issue based on the limited information. In order to trouble shoot this issue more efficiently, I need to clarify some information.

    • Can you try to execute the stored procedure in SQL Server Management Studio (SSMS)? What results can you get? If the stored procedure couldn’t be work well, you can try to refer to the suggestion which is posted by Sorna.
    • In order to reproduce this issue in my own environment, maybe I should know the function which is used in the stored procedure. Can you post the function?
    • Another possibility is that this phenomenon is can be caused by having added some filters in the Dataset in SSRS2012. So you can try to double-check this.


    If there are any misunderstanding, please elaborate the issue for further investigation.

    Thanks,
    Katherine Xiong

    If you have any feedback on our support, please click here.


    Katherine Xiong
    TechNet Community Support

    Hi,
    The function fn is the same on both machines.
    The stored procs sp on both machines is the same.
    Both sps on each machine return correct fields when run in query analyser.
    In designer of ssrs 2012, the sp does not return fields because of the function.
    The old deployed ssrs report on the same machine which uses the same sp does not return fields.


    Monday, January 13, 2014 9:28 AM