Thanks Isaac, saved me typing this.....lesson here....DONT use @@identity ;-)
-----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: 17 August 2005 15:48 To: CF-Talk Subject: RE: @@Identity returns excessive records > I haven't used this method in a long time because now I > use UUID()s as my unique record identifier and since I > create the UUID before I do the Insert, I have access to > it. > I don't have the syntax handy, but are you using the SET > NOCOUNT ON and SET NOCOUNT OFF? That might be it. > FWIW, I have read some research that @@IDENTITY will not > always provide you with the proper ID, especially in > multi-user environments (which the web most certainly is) > and leaves the possibility of returning the wrong IDENTITY > id. This is why I switched to using UUID's. Well the web is a multi-user environment... but generally speaking it doesn't appear to SQL server that way from CF. The ColdFusion server typically is a single user where SQL Server is concerned... at least that's how I understood it... But @@IDENTITY is not thread-safe... even within a single insert statement with nobody else using it, it's still possible to get the wrong ID... The test case for that is to create a table with an insert trigger which then inserts data into another table with an identity column. When you return @@IDENTITY the result will be the value inserted into the 2nd table (performed by the trigger) rather than the value you want from the first table. To resolve this Microsoft added the function SCOPE_IDENTITY() which can be used instead of @@IDENTITY and returns the last identity inserted into a table referenced in the current batch -- so in other words, if you execute a stored procedure or a trigger fires, identities inserted during those operations will be ignored in favor of an insert in the current batch or query (the code in which the SCOPE_IDENTITY() function is called). I've never seen @@IDENTITY return multiple rows, but that may be the result of the from clause... You might try omitting the from clause in that select statement... select @@IDENTITY as ID Although if you're using SQL2K I would definately switch to SCOPE_IDENTITY() s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:215561 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
