A couple of months ago, Rob Allen pointed out in a blog post that no Zend Framework PDO adapter existed for SQL Server. But, he also noted that it would be easy to write one...so he did: https://github.com/akrabat/Akrabat/blob/master/zf1/Akrabat/Db/Adapter/Pdo/Sqlsrv.php. Rob also mentioned that it would be very easy to use his adapter with the Zend Framework, but I wondered just how easy - that's what I'll investigate in this post. (Cut to the chase: it is very easy.)

Brian takes the long way around for those that want the details. He talks about setting up Zend_Tool for Windows, setting up the Zend Framework tutorial and creating the application. This includes setting up the Apache rewrite rules (mod_rewrite), adding in the database information to your application.ini file and creating a basic table on the SQL Server database to connect to (based on SQL included with the tutorial).

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 is that, when changes to settings are made, they don't seem to stick. Future queries act like they were never even updated. The solution to the problem is to use that PDO::SQLSRV_ATTR_DIRECT_QUERY attribute on the connection (set to true). You can see the difference in execution from this to this.