SQL 2000 the ID returned seem random but higher then expected ID value when using @@IDENTITY
but ID is null when using SCOPE_IDENTITY().... the ID field is an Auto Identity field. cheers joel -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Thornton Sent: Monday, 22 March 2004 4:13 To: CFAussie Mailing List Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY nope, no thoughts why messed up? are they close? are they way off? are they the same datatype? what database engine? >>> [EMAIL PROTECTED] 22/03/2004 3:24:07 pm >>> slightly off topic... but if you use cfquery param's with inserts and use scope or @@ identities... the id's you get are messed up..:((((((((((((((( without the cfquery params.. it seems fine... any thoughts on this???? cheers joel -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Thornton Sent: Thursday, 18 March 2004 11:56 To: CFAussie Mailing List Subject: [cfaussie] RE: New Record - SELECT @@IDENTITY Hi, If you have a trigger on your table, that inserts into another table, and if that table as an identity field, you will get THAT identity instead of the one you expect. SCOPE_IDENTITY() will return the correct identity I believe. Scott Thornton, Programmer Application Development Information Services and Telecommunications Hunter Area Health Service Phone +61 2 49214193 Fax +61 2 49214191 [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 18/03/2004 10:56:46 am >>> <cftransaction> <cfquery name="insertBlah" datasource etc> INSERT INTO tblBlah (name, address) VALUES ('Bill', 'Somewhere Street') </cfquery> <cfquery name="getIdentity" datasource etc> SELECT @@Identity as blahID </cfquery> </cftransaction> Then you use it in your code: getIdentity.blahID etc. HTH - J > -----Original Message----- > From: Yorke Hinds [mailto:[EMAIL PROTECTED] > Sent: Thursday, 18 March 2004 10:39 AM > To: CFAussie Mailing List > Subject: [cfaussie] New Record - SELECT @@IDENTITY > > > Not having used the SELECT @@IDENTITY regularly, > I was wonder if anyone can shed some light on this method > of retrieve the Record ID on INSERT. > DB: Ms SQLServer 2000 > > All comments & insight welcome! > > > > --- > You are currently subscribed to cfaussie as: > [EMAIL PROTECTED] > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
