I'm using php 5.5.7 and sqlsrv driver from microsoft
I attempted to insert multiple rows with single sql query which string size around 1MB But SQL server inserted only 1500 row which is only 220k of query string and give an error about incorrect syntax that seem to be truncated.
I never met this problem with mssql driver.
Did I do something wrong?
I don't know what the "limit" is but I had the same issue and just went with 4KB as the limit before writing the query to a file and then using the sqlcmd command line tool to import that file to SQL Server.
There doesn't appear to be any way to determine what the true limit is or adjust it.
if(strlen($query) > 4096) {
$fname = time() . rand(0,10000) . '.txt';
$fp = fopen($fname, 'w');
fwrite($fp, $query);
fclose($fp);
$output = [];
exec('sqlcmd -U' . MSSQL_USER . ' -P"' . MSSQL_PASS . '" -S' . MSSQL_HOST . ' -i' . $fname, $output);
$returnval['exec'] = implode(PHP_EOL, $output);
$returnval['error'] = '';
unlink($fname);
} else { sqlsrv_query(...); }
If you are using a single SQL Server INSERT statement with a list of values that is too long (The limit is 1000 rows in SQL 2008 R2, but your version of SQL Server may well be different in this respect), you will get an error. You need to break a single INSERT into multiple INSERTs with no more than the maximum number of VALUES, I.e.:
INSERT INTO tbl_x (RowNumber)
VALUES (1),
(2),
...
(999),
(1000);
INSERT INTO tbl_x (RowNumber)
VALUES (1001),
(1002),
...
(1999),
(2000);
and so on.
If using PDO, you could try a single parameterized prepared statement and loop through your individual inserts assigning the parameter array. This avoids the length issue, and having to create and pass a giant string in the first place. Seems like that would scale better than the heavy string too.
If I understand well, you are trying to execute a string query, from i.e. stored procedure. If I am not wrong, than it depends on the length of your declared string which contains that query. Please add more details/code.
