Hi Erland

> Then again, there are cases where @@identity will give you the correct
> result, and scope_identity() will not.

Could you give an example of when this might occur?

--
-Dick Christoph
dchristo@mn.rr.com
612-724-9282
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97A3F2A2F1723Yazorman@127.0.0.1...
> Tom Moreau (tom@dont.spam.me.cips.ca) writes:
>> Don't use @@IDENTITY. You can have incorrect results if your INSERT
>> fires a trigger which itself inserts into a table with an identity. Use
>> SCOPE_IDENTITY().
>
> Then again, there are cases where @@identity will give you the correct
> result, and scope_identity() will not.
>
> Now, I don't know how DAO works, but the suggestion to use
> scope_identity()
> relies on the somewhat risky assumption that .AddNew performs a straight
> insert. If DAO sets up a prepared query, run sp_executesql, or runs some
> temporary stored procedure, scope_identity will not work. Since DAO is
> a fairly old API, I would not expect it to be too sophisticated. Then
> again, using scope_identity() means that you rely on the implementation
> of something that could change with a service pack or a new release. (Not
> that such are bloodly likely for DAO.)
>
> Using @@identity is better, because it relies at least only on your
> own application and schema which you have more control over.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx