Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

executeUpdate Statement

Posted on by 10

Hello Experts 

I have an issue that i execute an insert into statement directly from X++ but the strange is that it inserts only 210 records while it suppose to insert more 

when i copy the query to sql and run it, it inserts the correct number of records 

appreciating your kind help

*This post is locked for comments

  • Rabea Jalamneh Profile Picture
    Rabea Jalamneh 10 on at
    RE: executeUpdate Statement

    i debug the code and copy the sql statement and run it in sql it inserts the complete dates between starting date and ending date

    i take the query with the values from the debugger also to make sure the variables is fine i create a stored procedure and replace the sql statement with it and also got the same result even i send the parameters as fixed values as follows

    execute SP_SOH 'ItemID','DataareaId','2018-01-01','2018-05-31'

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: executeUpdate Statement

    Then it's not the exact same query.

    How did you verify that it inserts 210 records? Did you get count of records in SQL Server?

    Are you sure your date variables have same values in both cases (not having any time zone differences)?

    It seems your query should insert as many records that are between BegDate and Edate. But we don't see the values of these variables.

    Also I don't see what is in other variables that you use.

    Perhaps you should compare what your select statement returns in AX and SQL, maybe you notice that you have some difference in the criteria (because of the variables).

    Basically the variables are the thing that can make all the difference, and we don't see the values of your variables.

  • Rabea Jalamneh Profile Picture
    Rabea Jalamneh 10 on at
    RE: executeUpdate Statement

    hi Nikolaos

    the problem is when i execute it from AX it inserts only 210 records but the same query in SQL inserts more than that

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: executeUpdate Statement

    If there were any limits, they would be the same, no matter if you execute this T-SQL in SQL Server Management Studio or AX.

  • Rabea Jalamneh Profile Picture
    Rabea Jalamneh 10 on at
    RE: executeUpdate Statement

    Dears,

    Is There any limitation for insert into statements if its used inside executeupdate ??

  • Rabea Jalamneh Profile Picture
    Rabea Jalamneh 10 on at
    RE: executeUpdate Statement

    hi Crispin

    i will try

    thank you

  • Rabea Jalamneh Profile Picture
    Rabea Jalamneh 10 on at
    RE: executeUpdate Statement

    hi Crispin

    The code Is below

    str             query,FDate,TDate;

       Set                                 permissionSet               = new Set(Types::Class);

       Connection                          connection;

       Statement                           statement;

       ;

       Fdate = date2str(BegDate, 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4, DateFlags::None);

       TDate = date2str(EDate, 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4, DateFlags::None);

       // Set the SQL statement

       query = "DECLARE @DATE DATE";

       query += " SET @DATE = '" + Fdate + "'";

       query += " truncate table ItemSOHRep";

       query += " WHILE @DATE <='" + TDate + "'";

       query += " BEGIN";

       query += "     INSERT INTO ItemSOHRep ";

       query += "     SELECT ITEMID, @DATE, sum(iTrans.QTY)   from INVENTTRANS as iTrans  inner join INVENTDIM as iDim on iDim.INVENTDIMID = iTrans.INVENTDIMID";

       query += "     inner join INVENTLOCATION Loc On Loc.INVENTLOCATIONID = iDim.INVENTLOCATIONID";

       query += "     where ";

       query += "     iTrans.DATAAREAID IN (SELECT  * FROM [dbo].[fnSplit]('" + Comps + "',',')) ";

       query += "     AND ";

       query += "     iDim.DATAAREAID IN (SELECT  * FROM [dbo].[fnSplit]('" + Comps + "',',')) ";

       if(Itm != "")

           query += "     and iTrans.ITEMID IN (SELECT  * FROM [dbo].[fnSplit]('" + Itm + "',',')) ";

       query += "     and";

       query += "     loc.GOODWAREHOUSE = 1 ";

       query += "     AND ITEMID NOT LIKE 'ZZZ%'";

       query += "     and ";

       query += "     ((iTrans.DATEINVENT <> '' and iTrans.DATEINVENT <= @DATE ) OR";

       query += "     (iTrans.DATEINVENT = '' and iTrans.DATEPHYSICAL <> '' and iTrans.DATEPHYSICAL <= @DATE ))";

       query += "     group by iTrans.ITEMID";

       query += "     SET @DATE = DATEADD(dd,1,@DATE)";

       query += " END";

       connection = new Connection();

       statement = connection.createStatement();

       permissionSet.add(new SqlStatementExecutePermission(query));

       CodeAccessPermission::assertMultiple(permissionSet);

       statement.executeUpdate(query);

       CodeAccessPermission::revertAssert();

  • Rabea Jalamneh Profile Picture
    Rabea Jalamneh 10 on at
    RE: executeUpdate Statement

    Hi Crispin

    The table i insert in is not AX Table i create it in SQL just to do aggregations

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,336 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans