PHP sqlsrv BULK INSERT incomplete

Question

I am trying to insert a big file (few millions row) via SQL server BULK INSERT functionality. My SQL query will look like:

 BULK INSERT MY_TABLE
 FROM '\\myserver\open\myfile.csv'
 WITH (
 firstrow=2,
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = '\n',
 BATCHSIZE = 50000,
 ERRORFILE = '\\myserver\open\myfileerror.log' 
 );

When I trigger it from MSSQL Server Management Studio, it always import it completely. When I do it from my PHP code, sometimes it stop in the middle, without any error messages.

I tried with both sqlsrv_query or sqlsrv_prepare/sqlsrv_execute, same result.

sql; //like the query above
$statement = sqlsrv_query($connection, $sql);
if($statement === false) {
    $error = sqlsrv_errors();
    $error['sql'] = $sql;
    throw new Exception(json_encode($error));
}

Would it be possible to get the logs of MSSQL from the $statement, the same I get from the MSSQL Studio? e.g. (50000 row(s) affected).

As a workaround, I have increased the BATCHSIZE to 1000000, but that is not a real solution.

Background information: - PHP 7.1.9 - sqlsrv version: 4.3.0+9904 - sqlsrv.ClientBufferMaxKBSize: 10240 - Windows 2012 R2 Server

How do you connect to SQL Server (how do you execute `sqlsrv_query()`)? — Zhorov, Jun 04 '20 at 17:42
I am not sure I got your question right. To connect to MSSQL, I use local account (so not Active Directory). I my code, I use the command sqlsrv_connect(database_url_name,$params) In $param, I have: ['CharacterSet'=>"UTF-8",'ReturnDatesAsStrings'=>true]. Else, I use a Windows Windows 2012 R2 server, and I installed MSSQL driver from: https://learn.microsoft.com/en-us/sql/connect/php/microsoft-php-driver-for-sql-server?view=sql-server-ver15 — Stilgar, Jun 05 '20 at 04:22
Thanks, I wanted to know how do you connect to SQL Server - using windows or sql authentication. But it seems, that your real problem is the fact, that the insert is not completely executed and there are no errors. I posted an [answer](https://stackoverflow.com/questions/62185448/does-sqlsrv-query-limit-the-number-of-statements-that-can-be-exectuted-in-one-qu/62189368#62189368) to a similar question, try to put `SET NOCOUNT ON` as first line in your statement. — Zhorov, Jun 05 '20 at 05:45

score 0 · Accepted Answer · answered Jun 06 '20 at 09:31

The issue was about the statement buffer. When I read it with sqlsrv_next_result, processing continue.

$statement = sqlsrv_query($connection, $sql);
if($statement === false) {
    $error = sqlsrv_errors();
    $error['sql'] = $sql;
    throw new Exception(json_encode($error));
} else if($statement) {
    while($next_result = sqlsrv_next_result($statement)){
        #echo date("Y-m-d H:i:s",time()). " Reading buffer...\n";
    }
}

PHP sqlsrv BULK INSERT incomplete

1 Answers1