ADO SQL Statement maximum string size?

Magno

Hi,

I have written a function to create an insert, update or delete statement from some tables. i have written this with recordrefs, so i can use a settings table that says which field to synchronise to which field in SQL, so with no effort, i can add fields/tables to synchronise.
I write this data to an Buffer table with an NAS to execute the sql statements.

I started with splitting the statement to parts (4 parts of 250chars & 1 part of 24 chars) so it fits 1 string in Navision.
About a year ago, i got to the point where i exceeded the 1024 character length in Navision. No problem, i added a second string and went to 2048 characters. also the buffer table changed to 10 parts...

No problem so far. when i execute, i now do

adoConn.execute(part1+part2+...+part10);

everything went well, until today. more fields to synchronise...
added an extra string, went to 3072 characters. code works fine. sql statement is correct in buffer over 15 parts now.
when i manually add the string to 1 in notepad, and run it in SQL Server, i executes, sounds great huh.

Until the NAS picks up the statement

runs:

adoCon.execute(part1+part2+...part15);

there it goes bad. get the message:

There is not sufficient Available space in the C/AL Stack memory to execute this task.

There are too many simultaneous activities, due to the way recursive function call is used in the program. The problem can be solved temporarily by shutting down some of the open activities (but the function still cannot be executed).

note that i didn't add the message in braces, it was in the error itself.

I don't know what the reason could be, since the first upgrade, to 2048 characters worked, it can't be of the string that is too long, also in SQL Server it works.

Does anyone have an idea what the error could mean, or better to solve this?

Thx.

Magno

Does anyone know what i can do?

garak

Is it possible to split the statments if the Characterlen > max. Bufferlen :?:

I mean, first you insert the record and second (third, ...) you update the fields :?:

This was my solution.

Regards

Magno

yes, that is exactly i'm busy doing now.

As i know my code,
i know delete statement is not likely to get too big

On the update statement, i isolate the "update <table> SET" & "WHERE X=x" parts en the fields in between are split between 2 statements of 2048.

The insert i'll have to write, but the same principle. Insert first, then update with remaining fields...

It seems to be the only solution.