Maximum number of statements in command object
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
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.
Hope this helps.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL