CODEWITHSUNDEEP
phpsqlsql-serverdatabase
Aneal Khimani
Aneal Khimani

Reputation: 43

PHP and SQLSRV isn't inserting all the rows it should

I'm utilizing PHP 5.5 and connecting to a MS SQL database. Connection works as expected. I'm attempting to do a mass insert (around 2000 rows). Rather than using PHP to run 2000 INSERT statements, I'm sending a query like so:

DECLARE @count INT
SET @count = 0
WHILE(@count < 2000)
BEGIN
INSERT INTO Table (Field1, Field2)
VALUES ('data1', 'data2')
SET @count = (@count + 1)
END

When I plug this query directly into SQL Server Mangement Studio, it inserts 2000 rows as expected. When I run the query via PHP like so:

$connectInfo = array ("UID"=>'user',"PWD"=>'@pass',"Database"=>"database", "ReturnDatesAsStrings"=>true);
$link = sqlsrv_connect("dataserver",$connectInfo);

$query = "DECLARE @count INT 
SET @count = 0  
WHILE(@count < 2000) 
BEGIN 
INSERT INTO License (Field1, Field2) 
VALUES('data1','data2') 
SET @count = (@count + 1) 
END";

if(!$foo = sqlsrv_query($link,$query)) {
  die(print_r(sqlsrv_errors(), true));
}else{
  die("hooray beer time!");
}

This only inserts 524 rows in the table, not the expected 2000 (and some times it's 309 rows, X rows, its erratic). It doesn't error out, and I get "hooray beer time!" after every script run. By default, sqlsrv_query() function has no time limits, I thought. Has anyone run into anything like this before? It has me baffled.

Upvotes: 1

Views: 496

Answers (1)

Aneal Khimani
Aneal Khimani

Reputation: 43

Ahh, it seems that after each insert in the While loop, SQL returns a little row of Text that says (1) row affected (or something to that tune). Turning this off via: 

SET NOCOUNT ON

has remedied this issue. Thanks all!

Upvotes: 2

Related Questions