Explanations:
There is a known issue with this driver, posted on GitHub, about executing large SQL statements. One part of the provided solution are the following explanations:
Seems like when executing a large batch of SQL statements, Microsoft SQL Server may stop processing the batch before all statements in the batch are executed. When processing the results of a batch, SQL Server fills the output buffer of the connection with the result sets that are created by the batch. These result sets must be processed by the client application. If you are executing a large batch with multiple result sets, SQL Server fills that output buffer until it hits an internal limit and cannot continue to process more result sets. At that point, control returns to the client. This behavior is by design.
Client app should flush all the pending result sets. As soon as all pending result sets are consumed by the client, SQL Server completes executing the batch. Client app can call sqlsrv_next_result() until it returns NULL.
So, I don't think that there is a limit for the SQL statement length, only the size of a PHP string variable ($InsertSQL in your case) is limited to the maximum allowed PHP memory limit. The actual reason for this unexpected behaviour is the fact, that with SET NOCOUNT OFF (this is by default) and a large number of single INSERT statements, the SQL Server returns the count of the affected rows as a result set (e.g. (1 row affected)).
Solution:
I'm able to reprodiuce this issue (using SQL Server 2012, PHP 7.1.12 and PHP Driver for SQL Server 4.3.0+9904) and you have the following options to solve this problem:
- Flush the pending result sets using
sqlsrv_next_result().
- Execute
SET NOCOUNT ON as first line in your complex T-SQL statement to stop SQL Server to return the count of the affected rows as a resultset.
- Use parameterized statement using
sqlsrv_prepare()\sqlsrv_execute()
Table:
CREATE TABLE MyTable (
Column1 int,
Column2 int,
Column3 int
)
One complex statement (using sqlsrv_query() and sqlsrv_next_result()):
<?php
// Connection info
$server = 'server\instance';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
"Database" => $database,
"UID" => $username,
"PWD" => $password
);
// Statement with sqlsrv_query
$sql = "";
for ($i = 1; $i <= 1000; $i++) {
$sql .= "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (".$i.", 0, 0);";
}
$stmt = sqlsrv_query($con, $sql);
if ($stmt === false) {
echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
exit;
}
// Clean the buffer
while (sqlsrv_next_result($stmt) != null){};
// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
echo "OK";
?>
One complex statement (using sqlsrv_query() and SET NOCOUNT ON):
<?php
// Connection info
$server = 'server\instance';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
"Database" => $database,
"UID" => $username,
"PWD" => $password
);
// Connection
$con = sqlsrv_connect($server, $cinfo);
if ($con === false) {
echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
exit;
}
// Statement with sqlsrv_query
$sql = "SET NOCOUNT ON;";
for ($i = 1; $i <= 1000; $i++) {
$sql .= "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (".$i.", 0, 0);";
}
$stmt = sqlsrv_query($con, $sql);
if ($stmt === false) {
echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
exit;
}
// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
echo "OK";
?>
Parameterized statement (using sqlsrv_prepare() and sqlsrv_execute()):
<?php
// Connection info
$server = 'server\instance';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
"Database" => $database,
"UID" => $username,
"PWD" => $password
);
// Connection
$con = sqlsrv_connect($server, $cinfo);
if ($con === false) {
echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
exit;
}
$sql = "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (?, ?, ?);";
$value1 = 0;
$value2 = 0;
$value3 = 0;
$params = array(&$value1, &$value2, &$value3);
$stmt = sqlsrv_prepare($con, $sql, $params);
if ($stmt === false ) {
echo "Error (sqlsrv_prepare): ".print_r(sqlsrv_errors(), true);
exit;
}
for ($i = 1; $i <= 1000; $i++) {
$value1 = $i;
$value2 = 0;
$value3 = 0;
$result = sqlsrv_execute($stmt);
if ($result === false) {
echo "Error (sqlsrv_execute): ".print_r(sqlsrv_errors(), true);
exit;
}
}
// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
echo "OK";
?>
