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) / SQL awaiting command w...
Microsoft Dynamics AX (Archived)

SQL awaiting command when running "Sales invoice report" to PDF or Screen

(0) ShareShare
ReportReport
Posted on by Chandra Wijaya Seti... 1,145

Hi All,

I have this problem when running SSRS report "Sales invoice"
SQL version: SQL 2016

AX version: AX 2012 R3 CU12  

It doesn't happen all the time.

I am pretty sure the lock is from the first SQL which is below with status "Awaiting command", it blocks other command for calling the report. 

(@P1 int,@P2 nvarchar(256))SELECT TOP 1 T1.SERVERID,T1.ISDEFAULTREPORTMODELSERVER,T1.SERVERURL,T1.ISDEFAULTREPORTLIBRARYSERVER,T1.AXAPTAREPORTFOLDER,T1.DESCRIPTION,T1.DATASOURCENAME,T1.REPORTMANAGERURL,T1.SERVERINSTANCE,T1.AOSID,T1.CONFIGURATIONID,T1.ISSHAREPOINTINTEGRATED,T1.RECVERSION,T1.RECID FROM SRSSERVERS T1 WHERE ((ISDEFAULTREPORTLIBRARYSERVER=@P1) AND (AOSID=@P2))

spid waittime lastwaittype database_name last_batch status program_name cmd object_name text
102 0 MISCELLANEOUS                    PRD 25:34.9 sleeping                       Microsoft Dynamics AX                                                                                                            AWAITING COMMAND NULL (@P1 int,@P2 nvarchar(256))SELECT TOP 1 T1.SERVERID,T1.ISDEFAULTREPORTMODELSERVER,T1.SERVERURL,T1.ISDEFAULTREPORTLIBRARYSERVER,T1.AXAPTAREPORTFOLDER,T1.DESCRIPTION,T1.DATASOURCENAME,T1.REPORTMANAGERURL,T1.SERVERINSTANCE,T1.AOSID,T1.CONFIGURATIONID,T1.ISSHAREPOINTINTEGRATED,T1.RECVERSION,T1.RECID FROM SRSSERVERS T1 WHERE ((ISDEFAULTREPORTLIBRARYSERVER=@P1) AND (AOSID=@P2))
114 5086548 LCK_M_U                          PRD 49:17.7 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 int)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE ((RECID=@P1) AND (RECVERSION=@P2))
210 4017283 LCK_M_U                          PRD 07:07.4 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 int)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE ((RECID=@P1) AND (RECVERSION=@P2))
238 5984533 LCK_M_U                          PRD 34:20.2 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 nvarchar(5),@P3 bigint)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (CREATEDTRANSACTIONID=@P3))
265 2771255 LCK_M_U                          PRD 27:53.6 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 int)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE ((RECID=@P1) AND (RECVERSION=@P2))
271 5630608 LCK_M_U                          PRD 40:14.3 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 nvarchar(5),@P3 bigint)DELETE FROM SALESINVOICETMP WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (JOURNALRECID=@P3))
342 6507120 LCK_M_U                          PRD 25:38.0 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 nvarchar(5),@P3 bigint)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE (((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (CREATEDTRANSACTIONID=@P3))
357 5217162 LCK_M_U                          PRD 47:08.0 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 int)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE ((RECID=@P1) AND (RECVERSION=@P2))
359 2314220 LCK_M_U                          PRD 35:31.0 suspended                      Microsoft Dynamics AX                                                                                                            DELETE           NULL (@P1 bigint,@P2 int)DELETE FROM SALESINVOICEHEADERFOOTERTMP WHERE ((RECID=@P1) AND (RECVERSION=@P2))

*This post is locked for comments

I have the same question (0)
  • Jie G Profile Picture
    Jie G on at

    Hi Chandra,

    You need to find the blocking head and which type of issue(wait_type) causes the blocking. You can make use of Activity Monitor(CTRL+ATL+A) or SQL DMV to do that.

    Best regards,

    Ada

  • Chandra Wijaya Setiawan Profile Picture
    Chandra Wijaya Seti... 1,145 on at

    Thanks Ada Ding

    The wait_type is miscellanous as per my table shown. The block head is the first statement i per i mentioned.
    From there what should i do?

    I know it is selecting SSRS parameter table, but why the status is awaiting command.

    It is not a dead-lock per say.

    FYI, I am not SQL expert so i  am not too sure what to do next, after i found out this information.

    Thanks. 

  • Chandra Wijaya Setiawan Profile Picture
    Chandra Wijaya Seti... 1,145 on at

    I would like to add additional info:

    1. Sales invoice SSRS is customized but only simple like the print layout and adding some field on salesinvoiceDP. I have done similar customization with No Issue.

    2. There is customization to print PDF by X++ on web service class.

    The PDF generation is using Static Method.

    public static fileName  PDFGenerate_SalesInvoice(custinvoicejour _custInvoiceJour, FilePath _filePath)
{
    SalesInvoiceContract            salesInvoiceContract   = new SalesInvoiceContract();
    SrsReportRunController          ssrsController = new SrsReportRunController();
    SRSPrintDestinationSettings     printerSettings;
    FileName                        fileName;

    ;
    if(!_custInvoiceJour.RecId)return fileName;



    //tell the controller the report to run (filename, design name)
    ssrsController.parmReportName(ssrsReportStr(SalesInvoice, Report));
    //define how we want to execute the report (right now or batch style)
    ssrsController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
    //hide the report dialog
    ssrsController.parmShowDialog(false);

    //we need to populate the required parms for the current sales order controller
    salesInvoiceContract.parmRecordId(_custInvoiceJour.RecId);
    salesInvoiceContract.parmCountryRegionISOCode(SysCountryRegionCode::countryInfo());
    salesInvoiceContract.parmPrintCopyOriginal(PrintCopyOriginal::Original);

    //link the contract to the controller so we know how to run the dp
    ssrsController.parmReportContract().parmRdpContract(salesInvoiceContract);

    //link the printer settings to the controller
    printerSettings = ssrsController.parmReportContract().parmPrintSettings();
    //print to pdf and always overwrite if the file exists
    printerSettings.printMediumType(SRSPrintMediumType::File);
    printerSettings.fileFormat(SRSReportFileFormat::PDF);
    printerSettings.overwriteFile(true);

    fileName    =  _filePath  + _custInvoiceJour.InvoiceId + "_" + _custInvoiceJour.SalesId + "_" + _custInvoiceJour.InvoiceAccount + ".PDF";

    printerSettings.fileName(filename);

    try
    {
        //run & save the report
        //ssrsController.startOperation();
        ssrsController.runReport() ;//refer to update at top of page
    }
    catch(Exception::Error)
    {

        Warning(strFmt("Error on saving report PDF, make sure SSRS server is valid, %1 / %2" , _custInvoiceJour.SalesId, _custInvoiceJour.InvoiceId));
    }
    return fileName;

}


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

    Usually select statement does not block delete statements, are you sure that you identified blocking statement ?

    Also with preprocessed reports you may have bunch of records not deleted from temp tables, that are not real temp tables, so deleting them manually (from SQL or x++)  may fix this issue.

  • Chandra Wijaya Setiawan Profile Picture
    Chandra Wijaya Seti... 1,145 on at

    Thanks Ievgen,

    [quote user="ievgen Miroshnikov"]

    Usually select statement does not block delete statements, are you sure that you identified blocking statement ?

    [/quote]

    I am pretty sure, few times i just kill that session and it released other statement to process. 

    However, I will check again when it happens again next time as i can't reproduce it in Test environment so far.

    [quote user="ievgen Miroshnikov"]

    Also with preprocessed reports you may have bunch of records not deleted from temp tables, that are not real temp tables, so deleting them manually (from SQL or x++)  may fix this issue.

    [/quote]

    I will check this out too, thanks. 

  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Killing session you are killing transaction as well, next time before killing it check what else do you have in this transaction.

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
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans