Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1.1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 326 Dynamics CRM
- 110 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 994 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
ADO SQL Statement maximum string size?
Magno
Member Posts: 168
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:
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.
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.
0
Comments
---
My Blog: http://NAV-Magno.be
I mean, first you insert the record and second (third, ...) you update the fields :?:
This was my solution.
Regards
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.
---
My Blog: http://NAV-Magno.be