when inserting a record into sql server (via odbc) doctrine fails to get last inserted ID

Microsoft SQL Server works in a such way, that different things have different scopes. Running query as prepared statement and then executing it results in different scope, hence running "select scope_identity()" after this query returns nothing. This is expected behaviour, and this is how Microsoft SQL Server should work.

To overcome this issue, I used a popular "hack" (maybe not really a hack) - add this query to the actual query (more here http://forums.devshed.com/ms-sql-development-95/insert-and-return-the-primary-key-232783.html or all other the web):

INSERT INTO X VALUES(1,1,2); SELECT SCOPE_IDENTITY();

Only using this way, I managed to make my component (Zend_Db_Adapter_Sqlsrv) to work. However, since Doctrine is very different and INSERT method is processed in completely different fashion I don't think that there is a way to overcome this issue.

unfortunately, retrieving the lastInsertedId() is part of the insert() functionality. So an exception is thrown everytime I try to insert a new record, which I must overcome (the record is by the way perfectly added to the db)

For me there is a perfect workaround: just use 'select @@IDENTITY' which works flawlessly.

So this is the point: I think more often 'select @@IDENTITY' must be chosen over 'select SCOPY_IDENTITY()' 'cause the latter does not (always?) return the id.

I googled some more:

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
http://beyondrelational.com/blogs/jacob/archive/2009/02/05/sql-server-identity-scope-identity-and-ident-current.aspx

Basically @@IDENTITY works in most cases, but it can go very wrong if you have triggers or stuff like that (which is not that rare in mssql world).

However, since with scope_identity() there are problems, I would say that Doctrine should use @@IDENTITY, because SCOPE_IDENTITY() will never return a value. Just impossible to fix.

Ah, I see, you are right, @@IDENTITY has to many drawbacks.

But in the first link you provided was the answer to overcome scope and trigger problems: the best option should be to actually use 'select IDENT_CURRENT('table_name')'. I just tested it and it works perfect! You only need to pass the table name in UnitOfWork.php in case the driver is mssql.

I've attached a possible fix.

Test it if you can and when approved I will commit it.

I tested your patch, but it didn't work since the $table var is empty in my case. This is because the function _assignIdentifier() in UnitOfWork won't set the $seq var in case of 'mssql'. So first of all we need to pass the table name to this function like this in UnitOfWork line 925:

+ } elseif ($driver == 'oracle' || $driver == 'mssql')

$id = $this->conn->sequence->lastInsertId($seq);

And furthermore I need to quote the table name before it worked like this in Mssql line 141:

$query = 'SELECT IDENT_CURRENT(\'' . $this->conn->quoteIdentifier($table) . '\')';

After these changes it worked. But don't you think we have to get rid of the SCOPE_IDENTITY entirely?
I attached this patch.

OK, what to do next? The last patch works fine for me. It seems to be the solution to the problem. So, can I check it in? Or should someone else do this? Or do I simply set the status to 'resolve' and someone else does the rest?

I tried the last patch and it works fine for me on SQL Server 2005/2008. +1 for committing it

Thanks for everyones work on this issue.

Tried the patch and it works on SQL Server 2005 as well. Lots of fun trying to find it. Thanks for the fix.