web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Table locked when mult...
Microsoft Dynamics AX (Archived)

Table locked when multiple users run the SSRS report at the same time

(0) ShareShare
ReportReport
Posted on by Community Member

Hello ALL,

I have developed a SSRS report using RDP class(extending 'SRSReportDataProviderPreProcess') and table(regular).  Also using the controller class to perform some post reportrun tasks (extending 'SrsReportRunController' class)

protected void postReportRun(SRSReportExecutionInfo _executionInfo)
{
    super(_executionInfo); // This should delete the data in table after the report has run according to my knowledge
}

RDP class processReport method
Everything is working fine when one user is running the report at a time, data is populated correctly and data is deleted from the table after the report has run.
But when two or more users run the report at the same time. data is being duplicated and not deleted after the report has run.
I tried to do the following in the RDP class Process Report method
public void processReport()
{
        ANT_AvalaraDailyReportContract  contract = this.parmDataContract() as ANT_AvalaraDailyReportContract;
   
    delete_from tmpAvalaraDailyReport
        where tmpAvalaraDailyReport.createdBy == curUserId(); // Making sure data is deleted before the report starts running
.......................
.......................
}
Deleting the data from the table for that user before the running of the report starts, but when i checked the SQL activity monitor,
the query (delete from table) is locking the table and making it to suspended status resulting in failure to to run the report.

Please advise on how to approach this issue when two users are running the report. Many thanks in advance

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    Regular table is not a recommend way to go. You need to use it only in scenarios like proforma. Use SrsReportDataProviderPreProcessTempDB instead, it will handle deletion for you without any extra code. Please read for details blogs.msdn.microsoft.com/.../improving-ssrs-report-performance-using-new-r3-features-part-6

  • Community Member Profile Picture
    Community Member on at

    Thanks ievgen for your response, The link you shared solved half of the problem. I am doing an excel export after the ssrs report is run. Will the TempDb support my cause here, since I was looking the data in regular table previously. Is there a way to preserve this data for my export process?

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    I don't think that you want to preserve it. How do you do it ? Is it custom code to get data from AX table or you export SSRS report to excel using SSRS capabilities ?

  • Community Member Profile Picture
    Community Member on at

    Its a custom code that does the excel export process selecting only few fields unlike all the fields in ssrs report. Another thing I don't see the class SrsReportDataProviderPreProcessTempDB class in R2 version. is it only available in R3?

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    It's in R3 only. Then you have to stick to preprocess class you have. Do you use currenttransactionid field there ? That how AX manage data between different sessions.

  • Community Member Profile Picture
    Community Member on at

    Yes I use CreatedTransactionId field, can we manage two users running the report at the same time without locking the table?

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    You locking table by your custom delete, you don't need it, because by default users should not see data from other runs, you need to sort this problem.

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,284 on at

    So if you will check SalesInvoiceDP class that uses regular table it has setTableConnections() method with next code:

    salesInvoiceTmp.setConnection(this.parmUserConnection());

    Do you have similar code in your DP ?

  • Community Member Profile Picture
    Community Member on at

    yes, I have the same kind of code

    public void processReport()

{

   ANT_AvalaraDailyReportContract  contract = this.parmDataContract() as ANT_AvalaraDailyReportContract;

   .....................................

.........................................

.........................................

   select count(RecId) from tmpAvalaraDailyReport1

       where tmpAvalaraDailyReport1.createdBy ==curUserId();

   if (tmpAvalaraDailyReport1.RecId != 0)

   {

       ttsBegin;

       delete_from tmpAvalaraDailyReport where tmpAvalaraDailyReport.createdBy == curUserId();

       ttsCommit;

   }

.....................................

.........................................

.........................................

}


  • Mea_ Profile Picture
    Mea_ 60,284 on at

    I don't see any usage of "setConnection" and you have to use it, so SSRS can split data between report runs.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Sukrut Parab Profile Picture

Sukrut Parab 2 Moderator

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans