FYI SET NOCOUNT ON/OFF for PHP MSSQL with temp tables
Posted: Tue Apr 04, 2023 2:06 pm
Hi All,
I chased my tail on this for a while, so I wanted to post an FYI. I'm not a PHP person, so this may be better known in the PHP world.
When I attempted to query a MSSQL server, using the: Stimulsoft.StiOptions.WebServer.url = "stimulsoft/handler.php";, and a stored procedure, the stored procedure call would not return my data.
The same call worked in SS management studio, but not through php / pdo_sqlsrv & sqlsrv.
The issue I found was that any stored procedure that had temporary tables, would not return my final query call.
The fix is to include:
SET NOCOUNT ON at the beginning of the sp
and
SET NOCOUNT OFF a the end of the sp
This makes sense as NOCOUNT will stop the server from sending the DONEINPROC message for each step of the sp.
I hope this helps someone.
g
I chased my tail on this for a while, so I wanted to post an FYI. I'm not a PHP person, so this may be better known in the PHP world.
When I attempted to query a MSSQL server, using the: Stimulsoft.StiOptions.WebServer.url = "stimulsoft/handler.php";, and a stored procedure, the stored procedure call would not return my data.
The same call worked in SS management studio, but not through php / pdo_sqlsrv & sqlsrv.
The issue I found was that any stored procedure that had temporary tables, would not return my final query call.
The fix is to include:
SET NOCOUNT ON at the beginning of the sp
and
SET NOCOUNT OFF a the end of the sp
This makes sense as NOCOUNT will stop the server from sending the DONEINPROC message for each step of the sp.
I hope this helps someone.
g