I cannot see in BOL where it says not to use it (I am on BOL SQL 2000 (Updated)). I certainly know that @@IDENTITY will give you the last known identity for any table / scope.
I don't use SCOPE_IDENTITY much but have done. -----Original Message----- From: Mike Kear [mailto:[EMAIL PROTECTED] Sent: 18 April 2005 14:10 To: CF-Talk Subject: Re: Last ID inserted Dont use @@Identity. Even the books on line (the online help for SQLServer) says it's risky in many circumstances. That was from an earlier version of SQLServer. The correct function to use is either Scope_identity() or Ident_current() . Dont take our word for it, read the books online entry. It seems the more we say DONT use @@Identity, the more people say DO use @@Identity. It's not MY words saying dont use it, its MICROSOFT's own help file that says @@Identity has risks of giving the wrong answer, especially when you have many inserts going on simultaneously. There'll no doubt be lots of people saying "well I've been using @@Identity and it's been fine for me". And the answer to that is you're lucky. Read Books on line about it. According to books on line, the correct query to use is: <cfquery name="insertquery" datasource="#application.dsn#"> SET NOCOUNT ON INSERT into myTable ( fieldname1, fieldname2, fieldname3) VALUES ( '#form.fieldname1#', '#form.fieldname2#', '#form.fieldname3#') SELECT Ident_Current('myTable') as NewID SET NOCOUNT OFF </cfquery> <p>New Identity is #insertquery.NewID#</p> Cheers Mike Kear Windsor, NSW, Australia Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On 4/18/05, Stuart Kidd <[EMAIL PROTECTED]> wrote: > Thanks for all your help on that everyone, the responses more than answered my question! :) > > When i get home i will put it into my code. > > Saturday > > ---------------------------------------- > From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> > Sent: 18 April 2005 07:42 > To: CF-Talk <[email protected]> > Subject: RE: Last ID inserted > > Triggers...?! ;-) evil evil evil... > > -----Original Message----- > From: JediHomer [mailto:[EMAIL PROTECTED] > Sent: 18 April 2005 12:36 > To: CF-Talk > Subject: Re: Last ID inserted > > Except that the @@IDENTITY is the last inserted ID, if you have any > triggers setup that insert then this could be affected, > Scope_Identity() locks the ID getting to the current scope/view so > remains unaffected by triggers etc > > On 4/18/05, Robertson-Ravo, Neil (RX) > wrote: > > SELECT @@IDENTITY is all you need. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203201 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
