If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Maximum number of statements in command object

Hi,
I was sending to an SQL 7 database a large chunk of INSERT INTO statements
in a single cn.Execute SQL statement, by wrapping them with a BEGIN TRANS/END
TRANS statement.

the code works fine till about 400 INSERT INTO statements, then it fail silently:
the transaction remain open (it seem SQL never get to the END trans statement)
and then timeouts.

It seems this is not related to the string size but rather to the number
of INSERT INTOs. Trimming the string size to about half size by omitting
the column list yeld the same limit of 400 statements, even if the Len(SQL)
is far shorter.

I cannot find this limit anywhere in SQL documentation. Is it a bug? Of course
I worked around it in the code, and I understand that sending more than 400
INSERT INTO in a single string isn't probably very smart, but I'm wondering
why the documentation don't state this as to be a limit.

Re: Maximum number of statements in command object

The problem may be that there is not enough space for log files, or the "inserted" or
"deleted" tables. When the statement fails ( hangs ), do at least the first few
records get inserted? Do you get "out of disk space" message if you let transaction
to stay open? These are the things to investigate if you are interested in exactly
what happens with 400 INSERTs.