Post reply

Nested Procedure embedded in SSRS

  • May 14, 2015 at 7:36 am

    #303864

    Hi,

    Is it possible to have a Nested Procedure in an embedded dataset?

    What I'm trying to do is execute a table update at the point a report is run and then display those updated records within the report (this data cannot be produced direct to SSRS live).

    So far I've tried

    EXEC PROC udp_TableUpdateALL (This calls 12 separate table updates)

    SELECT * FROM UpdatedTable

    Which works fine in SSMS but throws a timeout error in SSRS

    Any ideas?

    Regards
    ld

    Stoke-on-Trent
    United Kingdom

    If at first you don't succeed, go to the pub and drink away your current thought plan.

  • May 14, 2015 at 7:58 am

    #1797854

    Hi

    Imho you can create a new SP which calls udp_TableUpdateALL first and

    select statement after that.

    Regards

    Mike

  • May 14, 2015 at 8:12 am

    #1797864

    I thought as much, any ideas how to construct the query? it seems to be failing because no data is returned from the execute.

    Regards
    ld

    Stoke-on-Trent
    United Kingdom

    If at first you don't succeed, go to the pub and drink away your current thought plan.

  • May 14, 2015 at 8:13 am

    #1797865

    You are saying that you have a stored procedure with this code in it:

    EXEC PROC udp_TableUpdateALL (This calls 12 separate table updates)

    SELECT * FROM UpdatedTable?

    There is no reason that should not work. I have been working with SSRS for years and have seen/done stuff like that without any problem.

    I guess a few followup questions would be:

    What version of SSRS are you using? How long does it take to run the stored proc in SSMS? What is your timeout setting in SSRS?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 14, 2015 at 8:19 am

    #1797866

    Hi,

    I'm using SQL 2012

    It takes under 20 seconds in SSMS

    Timeout is 500 seconds

    Regards
    ld

    Stoke-on-Trent
    United Kingdom

    If at first you don't succeed, go to the pub and drink away your current thought plan.

  • May 14, 2015 at 9:10 am

    #1797888

    Problem Resolved.

    It seems that this error is caused by there being a difference in the ‘Time out’ between datasets, whilst the main dataset was set to 500 seconds the dataset which holds the Attribute formats was set to zero.

    As a random test I changed this dataset to 500 the same as the main dataset and the above error disappears, also changed them both to zero seconds and that also resolved the problem. (The code wasn't exceeding 500 seconds, it in fact only took about 30 seconds in total) 😀

    Regards
    ld

    Stoke-on-Trent
    United Kingdom

    If at first you don't succeed, go to the pub and drink away your current thought plan.

Viewing 6 posts - 1 through 5 (of 5 total)

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