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
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'
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.
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
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.
Dears,
Is There any limitation for insert into statements if its used inside executeupdate ??
hi Crispin
i will try
thank you
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();
Hi Crispin
The table i insert in is not AX Table i create it in SQL just to do aggregations
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,336 Most Valuable Professional
nmaenpaa 101,156