Hi, it's a scope problem. After the insert statement is executed the scope is lost for the next "select scope_identity()" (<- new scope outside the insert) Both statements, the insert and the select have to be executed within one call: "insert <table> (<columns>) values (<values>) select scope_identity()" prepare -> execute -> fetch -> a voila I use this solution in the products in our company. It works very well without any problems. If someone knows a better solution please email me.
It should be fixed in sqlalchemy I think. By the way: the workaround with select @@identity didn't fetch the correct value in some situations. For example if you have a trigger which inserts a new row in another table you will get the id from the insert in the trigger not your from your own insert statement. In worst cases you can also get the id from the insert of another user I think. Regards, Michael On 25 Mai, 21:07, "Jin Lee" <[EMAIL PROTECTED]> wrote: > Rick, Paul - > > Sorry for the late reply (my day job has been keeping me quite busy) > > It looks like ?use_scope_identity=0 works, I am no longer getting the error. > > Rick, to answer your questions, the pk is a field of type IDENTITY. I > am using pyodbc 2.0.35, and MSSQL 2000 SP 4 > > Thanks guys, > > Jin > > On 5/24/07, Paul Johnston <[EMAIL PROTECTED]> wrote: > > > > > > > Hi Jin, > > > >I keep getting this error after I do an insert. The insert is > > >successful, but I think there is a problem in getting the pk > > >afterward. > > > Yes, I'm afraid this is a known bug in 0.3.7. A workaround is to specify > > ?use_scope_identity=0 at the end of your database URL. > > > Paul- Zitierten Text ausblenden - > > - Zitierten Text anzeigen - --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
