I have spent hours trying to get to the bottom of this and it is driving me la la.
I have a simple stored procedure that just inputs 10,000 rows into a table. This is done because it takes long enough to prove the point (about 12 seconds).
create table lala (id int not null identity(1,1), txt varchar(100))
go
CREATE PROCEDURE lalatest
AS
BEGIN
DECLARE @x int;
SET @x = 10000;
WHILE @x > 0
BEGIN
INSERT INTO lala (txt) VALUES ('blah ' + CAST(@x AS varchar(10)));
SET @x = @x - 1;
END;
END;
When I run the stored procedure from within SQL Server Management Studio, 10,000 rows are inserted into the table.
When I run it from php it just aborts after about 150 rows (the number of rows varies, suggesting a timeout issue) with no error messages or any indication that it has finished early
The remote query timeout setting is set to the default of 600 seconds, so its not that.
The php:
$sql = "exec lalatest";
sqlsrv_query($cn, $sql);
I have tried specifying a timeout value (which should be indefinite by default anyway) on the sqlsrv_query line and it made no difference.
I'm using php 5.6.7
and Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)
I have all errors and warnings turned on
Anyone got any ideas as to what I need to do to make this work?
Would you believe it, I have made it work. I added SET NOCOUNT ON as the first line in the stored procedure and now it works from php as well.
Looks like having the rows affected info throws the php sqlsrv driver a wobbly. Strange how it only does it after a certain time (about a second), perhaps something to do with when the message buffer is flushed or something. Yes, I'm guessing, but this has solved the problem, so I'm happy.
CREATE PROCEDURE lalatest
AS
BEGIN
SET NOCOUNT ON; -- must be added so that it will run from php
DECLARE @x int;
SET @x = 10000;
WHILE @x > 0
BEGIN
INSERT INTO lala (txt) VALUES ('blah ' + CAST(@x AS varchar(10)));
SET @x = @x - 1;
END;
END;
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다