PHP INSERT INTO Table OR Table Variable Not Functional and No Error

Question

Good Afternoon, I have the following mock up script of my code. It appears that the following will not work with PHP and returns no error, but the query is sent to the SQL Server which I can see in a profiler trace. PHP Returns no response.

Ideas?

Will work with PHP:

SELECT * FROM TABLE

Will not work with PHP:

DROP TABLE #TMP1
SELECT 50 AS INT INTO #TMP1
SELECT * FROM #TMP1

Will not work with PHP:

DECLARE @TABLE TABLE 
(
ID INT
)

INSERT INTO @TABLE (ID)
SELECT 40

SELECT * FROM @TABLE

Will not work with PHP:

<?php  
$databaseName = "DB" ;
$serverName = "3424.34.234.3";   
$uid = "sdfsf";     
$pwd = "Jsdfsf";     


$connectionInfo = array( "UID"=>$uid,                              
                         "PWD"=>$pwd ,                              
                         "Database"=>$databaseName);   


/* Connect using SQL Server Authentication. */    
$conn = sqlsrv_connect( $serverName , $connectionInfo); 



$tsql = "
DECLARE @TABLE TABLE 
(
ID INT
)
INSERT INTO @TABLE (ID)
SELECT 40

SELECT * FROM @TABLE
";  



//EXEC PLAYER_LOOKUP  '$result';

/* Execute the query. */    

$stmt = sqlsrv_query( $conn, $tsql);  


    //Working Query
/* Iterate through the result set printing a row of data upon each iteration.    BR=."\n" */

  while($row = sqlsrv_fetch_ARRAY($stmt, SQLSRV_FETCH_ASSOC)){
      { 

    echo $row;


    }}  

/* Free statement and connection resources. */    
sqlsrv_free_stmt( $stmt);    
sqlsrv_close( $conn);    

?>

if you run your "$tsql" directly using SQL server management studio, it will work? — Felippe Duarte
– Felippe Duarte, Commented May 18, 2016 at 19:26

Michael Sandusky · Accepted Answer · 2016-06-22 21:44:04Z

1

all you have to do is to call SET NOCOUNT ON at the beginning of the stored procedure. This will prevent the "rows affected" messages to be sent to the client and be mistaken for the output of the procedure. Also you may have set it before a SELECT * INTO Statement.

answered Jun 22, 2016 at 21:44

Michael Sandusky

3712 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP INSERT INTO Table OR Table Variable Not Functional and No Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related