Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLCommand ExecuteNonQuery Maximum CommandText Length?

I've searched around the internet and everything seems to be about individual fields or doing one insert. I have a migration tool that is migrating from an old legacy database (superbase) to our SQL server DB (2008). Currently I'm reading 20,000 records from the old database and generating one big SQLCommand.CommandText string with 20,000 insert statements delimited by a semicolon. This works fine. But can I do 25k? 30k? I tried not having any limit at all, but when I tried to run ExecuteNonQuery with a CommandText containing over 4 million INSERT statements, it said something about being too long. I don't recall the exact error message, sorry. I can't find any documentation on the exact limitations. It's important for me to find out because the more inserts I can fire at a time, the faster the whole process. Any advice / links / documentation would be greatly appreciated, thanks!

like image 601
DontFretBrett Avatar asked Jan 27 '14 20:01

DontFretBrett


2 Answers

It depends on the SQL Server version.

http://technet.microsoft.com/en-us/library/ms143432.aspx

For SQL Server 2012, the batch size or SQL Statement (String) = 65,536 * Network packet size.

Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

In short, 64K x 4K = 256 MB. Again, this assumes you are using default packet sizes.

like image 141
CRAFTY DBA Avatar answered Oct 17 '22 03:10

CRAFTY DBA


A brief bing search showed me this link: http://dbaspot.com/sqlserver-programming/399616-what-max-length-sqlcommand-commandtext.html

It says:

The maximum size for a query batch is 65536 * network packet size. The default packet is 4096 bytes, which gives an upper limit of 268 million bytes.

It's unclear if that changes for different versions of sql server though.

like image 27
Tim Avatar answered Oct 17 '22 03:10

Tim