note 104263 modified in function.mssql-query by danbrown

I had this problem upgrading to sqlsrv from mssql. In short, SCOPE_IDENTITY doesn't seem to work with sqlsrv correctly when using parameters. This isn't in fact the case, it just works differently. To recap the problem: Table 'test' definitely has an identity column id. However you can not do (apologies for pseudo-code): <?php doquery("INSERT INTO test (col1, col2) VALUES (?,?)", $parameters); $lastId=doquery("SELECT SCOPE_IDENTITY()"); ?> because, under sqlsrv, these two queries are in different scopes, so there is nothing to return... However, this also doesn't work as expected: <?php $query = "INSERT INTO test (col1, col2) VALUES (?,?); SELECT SCOPE_IDENTITY()"; $arrParams[]="1"; $arrParams[]="2"; $resource=sqlsrv_query($conn, $query, $arrParams); $arrRow = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC); echo $arrRow[0]; ?> Here you might expect to see the last insert id, but you see nothing. This is because the resource is currently pointing to the 'true' from the first query (the insert query) and so the insert id is 'hidden' in another row of the resource. Solution.... set up as before, but then: <?php $resource=sqlsrv_query($conn, $query, $arrParams); sqlsrv_next_result($resource); sqlsrv_fetch($resource); echo sqlsrv_get_field($resource, 0); ?> This will give you what you want! This will work similarly with something like "INSERT INTO test (col1, col2) VALUES (?,?); SELECT * from test WHERE ID = SCOPE_IDENTITY()" and again you have to move through the resource to get at the data you want. I hope this helps someone, since it's just wasted a good two hours of my time! Also, sorry for any errors in the code - I'm afraid I've written it off the top of my head, but hopefully you get the idea... --was-- Hi, I had this problem upgrading to sqlsrv from mssql, but I can't find the appropriate area on php.net to put this, so I'm putting it here, since it drove me mad, and this is the top google result for my search! In short, SCOPE_IDENTITY doesn't seem to work with sqlsrv correctly when using parameters. This isn't in fact the case, it just works differently. To recap the problem: Table 'test' definitely has an identity column id. However you can not do (apologies for pseudo-code) doquery("INSERT INTO test (col1, col2) VALUES (?,?)", $parameters); $lastId=doquery("SELECT SCOPE_IDENTITY()"); because, under sqlsrv, these two queries are in different scopes, so there is nothing to return... However, this also doesn't work as expected: $query = "INSERT INTO test (col1, col2) VALUES (?,?); SELECT SCOPE_IDENTITY()"; $arrParams[]="1"; $arrParams[]="2"; $resource=sqlsrv_query($conn, $query, $arrParams); $arrRow = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC); echo $arrRow[0]; Here you might expect to see the last insert id, but you see nothing. This is because the resource is currently pointing to the 'true' from the first query (the insert query) and so the insert id is 'hidden' in another row of the resource. Solution: .....set up as before, but then: $resource=sqlsrv_query($conn, $query, $arrParams); sqlsrv_next_result($resource); sqlsrv_fetch($resource); echo sqlsrv_get_field($resource, 0); This will give you what you want! This will work similarly with something like "INSERT INTO test (col1, col2) VALUES (?,?); SELECT * from test WHERE ID = SCOPE_IDENTITY()" and again you have to move through the resource to get at the data you want. I hope this helps someone, since it's just wasted a good two hours of my time! Also, sorry for any errors in the code - I'm afraid I've written it off the top of my head, but hopefully you get the idea... http://php.net/manual/en/function.mssql-query.php