AX send bad request to SQL Server

This question is answered

Hello,

I work with Dynamics AX2009 and I'm face to a problem that I can't understand. It seems that AX have a problem to correctly generate SQL query when we do some calculations in the WHERE.

I want to execute this X++ query :

WHILE SELECT salesId, LineNum, salesQty, SalesPrice, linePercent, LineAmount
FROM SalesLine
WHERE SalesLine.salesId >= 'K0100000'
&& salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(1 - salesLine.linePercent/100) + 0.01)
{
}

But if I create a trace in SQL Server Profiler for survey what is send to it, I can see this :

declare @p1 int
set @p1=1076836550
declare @p2 int
set @p2=195416459
declare @p5 int
set @p5=16
declare @p6 int
set @p6=1
declare @p7 int
set @p7=2
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21),@P3 bigint,@P4 numeric(28, 12),@P5 bigint',N'SELECT A.SALESID,A.LINENUM,A.SALESQTY,A.ITEMID,A.LINEAMOUNT,A.SALESPRICE,A.LINEPERCENT,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND ((SALESID>=@P2) AND (LINEAMOUNT>(((SALESPRICE*SALESQTY)*(@P3-(LINEPERCENT/@P4)))+@P5))))',@p5 output,@p6 output,@p7 output,N'kl',N'K0100000',100,1.000000000000,254
select @p1, @p2, @p5, @p6, @p7

if I rebuild the query it give :

SELECT A.SALESID,A.LINENUM,A.SALESQTY,A.ITEMID,A.LINEAMOUNT,A.SALESPRICE,A.LINEPERCENT,A.RECID
FROM SALESLINE A
WHERE DATAAREAID = N'kl'
  AND SALESID >= N'K0100000'
  AND LINEAMOUNT > (SALESPRICE*SALESQTY* (100-LINEPERCENT/1.00)) + 254

Which is not the same at all ! We can see that the 1 and the 100 have been exchange (i did some tests, it seems really to be an exchange) and the 0.01 and been change into 254 ?!

Does someone ever seen something like that ? And how can I be sure that ALL of the other queries send are OK ?


Thanks.

Ps : Sorry for mistakes, I'm French

Verified Answer
  • Hi,

    I re-open this post because I had the solution.

    I send this problem to our Hotline, and they first answered that there was already a fix for that. We installed it and upgrade the kernel version from 5.0.1500.6491 to 5.0.1600.1616. But finally this corrected only half of the problem. After that, I didn't have anymore the replacement of 0.01 by 254. But I still had the exchange between 1 and 100. So I was in the same case of Tommy Skaue.

    So I re-mailed them which escalate it to the Microsoft development team. After several months they finally publish a new Hotfix which correct the bug. We are now in Kernel version 5.0.1600.2031.

    Thank you all for having tried to help me.

All Replies
  • Try to Synchronize the table from the AOT.

    You could also try flush the Procedure Cache on the SQL Server, tho not during business hours.

    SQL Server command is:

    DBCC FREEPROCCACHE

    (blog.sqlauthority.com/.../sql-server-stored-procedure-clean-cache-and-clean-buffer)

    Not sure tho.

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • Thanks for reply.

    I've tried DBCC FREEPROCCACHE but nothing changed, I have always the problem.

    I tried the same query but on the table custInvoiceTrans instead of salesLine and the problem is the same. I tried too on a new table with only the fields I need, again the same. So it's not related to the table.

    One thing interesting : If I add "forceLiterals" on my query the result is a bit different, I always have the exchange between 1 and 100, but the 0.01 is good. But anyway, it counldn't be a solution because the final objective is to create an alerte based on a specific filter (in which we can't specify forceLiterals).

  • Could you try, just as a sanity check, wrap your atomic values with query range expressions?

    www.axaptapedia.com/Expressions_in_query_ranges

    It's a bit of hazzle to convert your query using the Query object, but could you try and see if it makes any difference?

    It really does sound like the SQL Server is picking a bad execution plan, but I don't see why.

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • I checked into this on AX2012 R2 and the issue exists there as well.  By using the generateonly keyword on the select statement (also with forceLiterals), it shows that the generated SQL statement is incorrect (100 and 1 are exchanged, 0.01 is correct):

        select firstOnly generateonly forceLiterals SalesId from salesLine
            where salesLine.SalesId >= '1000'
                && salesLine.LineAmount > (salesLine.SalesPrice * salesLine.SalesQty * (1 - (salesLine.LinePercent / 100)) + 0.01);

    SELECT TOP 1 T1.SALESID,T1.RECID FROM SALESLINE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'usmf')) AND ((SALESID>=N'1000') AND (LINEAMOUNT>(((SALESPRICE*SALESQTY)*(100-(LINEPERCENT/1.E0)))+1.E-2))))

    When capturing a trace with SQL Server Profiler while running this code, I could not find any reference to this hitting the SQL server.  It looks like it is an AOS issue.

  •  I've already tried that, in fact it's the origin of my problem.

    Not in a queryBuildDataSource with queryBuildRange, but in filter on an alert rule. I made a filter with the syntax describe in Advanced filtering and query options (SQL statement between parenthesis) so it's the same thing of expression in query ranges but it doesn't work. From there, I prospected farther, and that's how I discovered this problem...

  • OK, so same problem in AX2012, it's not really comforting...

    So what can we do face to that kind of bug ?

    If I change "(1 - (salesLine.LinePercent / 100)) "  by "(salesLine.LinePercent * (-0.01) + 1)" it works, but it's not really a good solution...

    And I always have the transformation of "+ 0.01" in "+ 254" when I use it in a standard filter. Here, I would think it's byte problem (254 = 2^8 - 2 ?).

  • Wow. That is a really kewl bug (not)...

    I did a quick test and a trace as well. Sure enough, the kernel does something weird with those numbers.

    Something for the Support Team to have a look at. ;-)

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • the expression with salesline.a < salane.b+salesline.c, etc can't work.

    in case you would like to do it follow steps in next link

    www.axaptapedia.com/.../Expressions_in_query_ranges

  • It seemed to work if I moved the parameter around:

    See how I change the "1 -" to after dividing the percentage. The result is the same, but now it works.
     

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • Thank you to take time for me.

    Tommy, your formula is not the equal to mine :

    A*B*(C/100 +1) + 1.011 != A*B*(1-C/100) + 0.01 (just take A=B=C=1, and you will see it doesn't work)

    Dick, I read the article on Axaptapedia, but I don't how I could do the same comparison without this calculation. And first, the X++ code compiles and second, if I remove number in my calculation, it works fine...

    Tommy, something appeals to me, in your screen capture, I see that you don't put forceLiterals and the last number 0.01 is good in SQL. If I do exactly the same thing, me, I have 254 instead of 0.01. So it might have difference somewhere...

  • The formula is supposed to be:

    A*B*(C/100 -1) + 1.011

    The only thing I changed was switching the place of "minus 1" to after C/100 instead of in front.

    In other words:

    (1 - (salesLine.LinePercent/100) )

    vs

    ((salesLine.LinePercent/100) - 1)

    Regarding forceliterals I don't think they matter, as it seems to be a problem on the AOS side, as Dick says. Actually I had to bind the variables when running the Trace within AX2012 in order to see the values being used in the query.

    So in conclusion, try:

    WHILE SELECTsalesId, LineNum, salesQty, SalesPrice, linePercent, LineAmount
    FROM SalesLine
    WHERE SalesLine.salesId >= 'K0100000'
    && salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*((salesLine.linePercent/100)-1) + 0.01)
    {
    }

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no

  • i did some checks

    these work fine, I know in ax 2009 and before it did not work

    OK

    static void Job1(Args _args)

    {

       CustInvoiceJour CustInvoiceJour;

       while select CustInvoiceJour where CustInvoiceJour.SalesBalanceMST < (1 + (CustInvoiceJour.SalesBalanceMST ) )

       {

        info(   CustInvoiceJour.InvoiceAccount);

       }

    }

    roundoffs

     while select CustInvoiceJour where CustInvoiceJour.SalesBalanceMST == (1 + (CustInvoiceJour.SalesBalanceMST/100  )*100 -1 )

       {

        info(   CustInvoiceJour.InvoiceAccount);

       }

    but just a check

    incase you move the where condition to if condition in the while loop, just to be Shure, do you get the expected data?

  • Dick, I did the check and the query doesn't work :

    while SELECT salesId, LineNum, salesQty, LineAmount, SalesPrice, linePercent
    FROM salesLine
    WHERE salesLine.salesId >= 'K0100000'
       && salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(1 - salesLine.linePercent/100) + 0.01)
    {
        info(salesLine.SalesId + "/" + int2str(salesLine.LineNum));
    }

    Even though this works :

    while SELECT salesId, LineNum, salesQty, LineAmount, SalesPrice, linePercent
    FROM salesLine
    WHERE salesLine.salesId >= 'K0100000'
    {
        if (salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(1 - salesLine.linePercent/100) + 0.01))
        {
            info(salesLine.SalesId + "/" + int2str(salesLine.LineNum));
        }
    }

    Tommy, (1 - (salesLine.LinePercent/100) ) is not the same as ((salesLine.LinePercent/100) - 1). In the first case you get a positive value, in the second you get a negative. If you want the same you must muplicate by -1.

    And if I execute this :

    while SELECT salesId, LineNum, salesQty, LineAmount, SalesPrice, linePercent
    FROM salesLine
    WHERE salesLine.salesId >= 'K0100000'
       && salesLine.LineAmount > (salesLine.SalesPrice*salesLine.salesQty*(salesLine.linePercent/100 - 1)*(-1) + 0.01)
    {
        info(salesLine.SalesId + "/" + int2str(salesLine.LineNum));
    }

    In SQL Trace, the division by 100 and the subtraction by 1 is OK but the multiplication by -1 is changed in 4294967295 and the addition of 0.01 is even changed in 254 :

    exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(21),@P3 numeric(28, 12),@P4 bigint,@P5 bigint,@P6 bigint',N'SELECT A.SALESID,A.LINENUM,A.SALESQTY,A.LINEAMOUNT,A.SALESPRICE,A.LINEPERCENT,A.CUSTACCOUNT,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND ((SALESID>=@P2) AND (LINEAMOUNT>((((SALESPRICE*SALESQTY)*((LINEPERCENT/@P3)-@P4))*@P5)+@P6))))',@p5 output,@p6 output,@p7 output,N'kl',N'K0100000',100.000000000000,1,4294967295,254

  • I forwarded this discussion to MS core team. there response was "looks BAD"  So I think they say, we have a bug

  • @Xavier: You're absolutely right. It needs to be multiplied with -1 again. Stupid mistake from me.

    My tests were on AX2012 RTM (6.0.1108.670).

    +1 to the MS core team. ;-)

    Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no