MSSQL Server: Enabling MARS prevents the saving of records.

We'll need some more information. We don't have a way to test against mssql currently.

I've dug a little deeper and it seems that the exception is thrown because the Doctrine_Transaction_Mssql class has overrides for the transaction begin/commit/rollback methods. When I remove these overrides and let transactions be handled through the PDO API I don't get any exceptions while transactions seem to function as normal.

Why are these methods overridden? Is it perhaps only necessary for the MSSQL PDO extension, as suggested here (http://trac.symfony-project.org/wiki/HowToConnectToMSSQLServer), since that extension seemingly has no native transaction support (I've never used it)? Perhaps this isn't necessary for the ODBC extension?

This patch maintains the existing manual transaction functionality for MSSQL connections, but uses the native PDO API for ODBC connections.

Hmm. I don't think this patch is good. It doesn't fix the problem, just hacks around it.

Ok.. Do you have any recommendations for a better patch? While I agree my patch is far from elegant, it does at least allow MSSQL and ODBC to work alongside each other. As far as I can tell, the underlying issue is that Doctrine uses the same driver for two very different PDO extensions - I couldn't think of a way to resolve this issue without some kind of check as to which extension is in use.

We could maybe catch the exception that's thrown in the first instance when running a manual query, and try calling the parent rollback method instead? Maybe only when the current connection is ODBC? That's essentially the same approach though, with the added overhead of an unnecessary query that we know will fail..

I'm very keen to find a fix that you're happy with for this since I've been manually patching it for a while, so any feedback that would engender that would be great!

I am still not sure of the exact problem/error that is happening. When isn't happening with the current code? or is something happening that should not?

As far as I understand it (I'm no SQL Server expert, unfortunately!) the issue is this:

The Doctrine_Transaction_Mssql class is used for both the MSSQL and ODBC PDO extensions. It manages transactions by executing manual queries against the database (ROLLBACK TRANSACTION, COMMIT_TRANSACTION, etc.).

This isn't compatible with the ODBC PDO extension and SQL Server when Multiple Active Record Sets is enabled; I believe MARS allows multiple transactions to co-exist, and this query-style way of managing them isn't compatible with that as transactions aren't 'namespaced'. MARS is critical because without it there's no way of looping over more than one record set at the same time, something we take for granted in MySQL et al.

From that link to the Symfony wiki I posted earlier, it appears that the query method of managing transactions is only necessary for the MSSQL PDO extension - quote:

"dblib doesn't support transactions so we need to add a workaround for transactions, last insert ID, and quoting"

This is backed up by the patch - when we let the ODBC driver handle transactions natively, everything works fine.

Does that make sense?

Now that I look at everything again, do we need to even be overriding those methods at all in Doctrine_Transaction_Mssql (_doRollback, _doCommit, _doBeginTransaction). We should just let the database driver handle it?

Index: lib/Doctrine/Transaction/Mssql.php
===================================================================
--- lib/Doctrine/Transaction/Mssql.php	(revision 7673)
+++ lib/Doctrine/Transaction/Mssql.php	(working copy)
@@ -65,28 +65,4 @@
 
         $this->conn->execute($query);
     }
-    
-    /**
-     * Performs the rollback.
-     */
-    protected function _doRollback()
-    {
-        $this->conn->getDbh()->exec('ROLLBACK TRANSACTION');
-    }
-    
-    /**
-     * Performs the commit.
-     */
-    protected function _doCommit()
-    {
-        $this->conn->getDbh()->exec('COMMIT TRANSACTION');
-    }
-    
-    /**
-     * Begins a database transaction.
-     */
-    protected function _doBeginTransaction()
-    {
-        $this->conn->getDbh()->exec('BEGIN TRANSACTION');
-    }
 }

It seems like we don't even need to do that?

For ODBC, we definitely don't. That link suggests the above approach caters to the MSSQL extension. I've not tried that one because I'm on Windows and as you probably know it's not recommended; I imagine I'd have way too many other problems were I to try! I didn't want to break anything for anyone using the MSSQL driver though, which led to my awkward patch.

From my point of view, I'd imagine the majority of people using SQL Server will be using a Windows stack, and therefore ODBC (and possibly the new SQL Server Driver for PHP PDO extension, which definitely won't need manual queries, in future) - so I'd favour an approach that didn't compromise those drivers.

As an aside - are there any plans afoot to support the new Microsoft endorsed driver? I imagine if you guys were to do so it would be for Doctrine 2, but do you know of any community effort to get it working in 1.2?