I've two issues with saving records with SQL Server.
Firstly, SQL Server does not allow NULL or DEFAULT values to be used against identity columns. When inserting a record without any modified fields, Doctrine_Record_UnitOfWork::processSingleInsert sets all fields to NULL - including the identity columns. This causes this error:
"SQLSTATE: Syntax error or access violation: 339 [Microsoft][SQL Server Native Client 10.0][SQL Server]DEFAULT or NULL are not allowed as explicit identity values"
Ideally identity columns could be excluded but that would break tables that consist of those columns. Instead I've patched Doctrine_Connnection_MSSQL to, in this 'blank insert' circumstance only, insert default values and then update the inserted row with null values as necessary (the latter may be unnecessary but I wanted to preserve the original behaviour).
Secondly, there exists a bug in (as I understand it) Microsoft's ODBC driver , that causes all bound parameters within a sub-select to be cast as strings before being interpreted by SQL Server. This results in the following error:
SQLSTATE: Invalid character value for cast specification: 206
When using SQL Server through ODBC all Doctrine queries that use sub-selects are failing, for example the query generated by a Doctrine_Table->find()
SELECT * FROM (SELECT TOP 1 * FROM (SELECT TOP 1 [m].[id] AS [m__id], [m].[name] AS [m__name] FROM [model] [m] WHERE ([m].[id] = ?)) AS [inner_tbl]) AS [outer_tbl]
To combat this I've extended the Doctrine_Connection_Common execute() method in Doctrine_Connection_Mssql to replace bound parameters so they're inline instead. This has its drawbacks, like reduced protection against SQL injection, but since the alternative is the majority of my queries failing I don't see a better option.
I realise this second issue isn't a Doctrine concern per se, but I thought I'd bring it up since I imagine anyone using SQL server will be experiencing it.
A patch against 1.2.1 is attached. This patch also incorporates the latest patch in http://www.doctrine-project.org/jira/browse/DC-289, which has now been committed to trunk; my additions are at the bottom of the file.