How to Change Database Settings with the PDO_SQLSRV Driver
This post is based on another lesson I learned at the JumpIn! camp in Redmond a few weeks ago. The short story is this: If you are using the PDO_SQLSRV driver and you want to execute a query that changes a database setting (e.g. SET NOCOUNT ON), use the PDO::query method with the PDO::SQLSRV_ATTR_DIRECT_QUERY attribute. If you use PDO::query without that attribute, the database setting you expected to change may not be applied to subsequent queries. The longer story has to do with why this is the case, which is what I’ll explain in this post.
The Problem
Consider the following code which appears to execute a query that turns on the NOCOUNT setting (which you might want to do if you don’t want the server returning information about rows affected by a query):
$server = 'serverName';
$conn = new PDO("sqlsrv:Server=$server; Database=TestDB", "user", "password");
$conn->query("SET NOCOUNT ON");
If you follow the code above with this code…
$tsql = "INSERT INTO Table_1 (id, data) VALUES (?, ?)";
$stmt = $conn->prepare( $tsql );
$stmt->execute(array(1, "data"));
$rowCount = $stmt->rowCount();
echo "Row count: ".$rowCount."<br/>"; //1, NOCOUNT is off.
…you will find that the number of rows affected by the query is, in fact, returned. In other words, NOCOUNT is still off…executing SET NOCOUNT ON did not appear to work. This can happen for any number of queries that are meant to change a database setting (to see more examples of such queries, see SET Statements (Transact-SQL)).
Note: At JumpIn! camp, we originally discovered this problem when trying to change the QUOTED_IDENTIFIER setting, which determines whether identifiers can be enclosed in double quotes (e.g. SELECT * FROM “Order Details”). However, we later realized we didn’t need to execute a query for this particular setting; it can be set when you establish your connection to the database by setting QuotedId property in your DSN string. For more information, see Connection Options in the driver docs.
Why does this happen?
The reason that “the problem” exists is simple, but not necessarily intuitive. When a query that changes a database setting is executed within a stored procedure, the scope of the setting is limited to the stored procedure. And, when PDO::query is called, under the covers it calls sp_prepexec, a system stored procedure. Turning on SQL Profiler make it easy to see this:
The NOCOUNT setting is on, but only for the sp_prepexec stored procedure call, not for the query we wanted it to apply to.
The Solution
Fortunately, the solution is easy. If you call PDO:query with the PDO::SQLSRV_ATTR_DIRECT_QUERY, like this…
$server = 'serverName';
$conn = new PDO("sqlsrv:Server=$server; Database=TestDB", "user", "password");
$conn->query("SET NOCOUNT ON");
$conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true);
$conn->query("SET NOCOUNT ON");
$tsql = "INSERT INTO Table_1 (id, data) VALUES (?, ?)";
$stmt = $conn->prepare( $tsql );
$stmt->execute(array(1, "data"));
$rowCount = $stmt->rowCount();
echo "Row count: ".$rowCount."<br/>"; //-1, NOCOUNT is on.
…the PDO_SQLSERVER driver follows a different code path…one that doesn’t call sp_prepexec. The query is executed directly and the NOCOUNT setting (or whatever setting you are trying to change) applies to the connection. Again, this is easy to see in SQL Profiler:
Note: $rowCount in the code above is –1, since that what PDOStatement::rowCount returns when no affected rows are returned.
So, once again, the short story is…if you are using the PDO_SQLSRV driver and you want to execute a query that changes a database setting (e.g. SET NOCOUNT ON), use the PDO::query method with the PDO::SQLSRV_ATTR_DIRECT_QUERY attribute. Hopefully, understanding why is at least interesting, and maybe even helpful.
Thanks.
-Brian