SCOPE_IDENTITY via an ODBC connection

in order provide higher availability our internal intranet, upgrading our sql environment single sql 2005 server 2 mirrored sql 2008 servers.  part of process, updating of our php code use dsn connection instead of mssql functions take advantage of mirroring without writing custom solution.  however, during process, have run important problem hoping can shed light on.

 

in our database classes, after perform insert, use scope_identity() function return primary key of record inserted.  although works using php mssql functions, no longer works when use odbc connection.  based on our testing, both @@identity , ident_current(‘tablename’) work, scope_identity() not.  unfortunately, cannot use @@identity because every change in our database logged in audit table means @@identity returns primary key of our audit table instead of record inserted.  cannot use ident_current(‘tablename’) because works across connections , not connection of user performing insert.  ident_current(‘tablename’) method make possible 2 users insert records simultaneously , have 1 user receive wrong primary key.

 

in our testing of problem, tried using sql 2005 , 2008 server , native clients.  additionally, tried testing against our sql 2008 sp1 , our sql 2005 sp3 servers exact same results.  stands, @ loss how make scope_identity() work correctly.  in looking through documentation, discovered none of above functions intended work across linked server connection.  in our environment, our intranet website hosted on same server our sql server.  means cannot using remote connection, appears sql treating our connection linked (remote) connection instead of local connection.

 

does have ideas of suggestions can try resolve problem?  appreciated.

hi okeefe_david,
1. mean scope_identity() doesn't work? return null or other value?
2. can collect profile log , let's know how use scope_identity. 
    know scope_identity() limited only inside 1 scope. means scope_identity() must closely appended end of insert statement.  such " insert table (x,x) valuex(v,v);select scope_identity() identity_id". doesn't make sense call "select scope_identity identity_id" separate statement.

SQL Server  >  SQL Server Data Access