I just discovered that sql server 2000 has a cool function that captures
the identity value of an insert.
SELECT SCOPE_IDENTITY()
This new function returns the last IDENTITY value produced on a
connection and by a statement in the same scope, regardless of the table
that produced the value
INSERT YakName VALUES ('Billy Joe Bob')
SELECT SCOPE_IDENTITY() --returns the value 2
SCOPE_IDENTITY() works for all tables in the scope for which it was
called, which in this case is the original batch. So, we get the last
value for the YakName table, which is what we wanted.
The above is quoted from a discussion of @@identity on the
www.sqlteam.com web site. Hope this is useful.
John
Scott Cadillac wrote:
>
> Thank you Klaus, for clearing that up in simple terms.
>
> Although I don't use @@IDENTITY very much, I find it a very useful function
> but I admit I've wondered about this point. I've read the SQL On-line
> documentation, but frankly it didn't clarify things when I tried to match up
> SQL-Server's explanation of a Session and what we understand is a Witango
> Thread.
>
> Are they the same thing? My guess is that they are not - correct?
>
> Does a single SQL 'Session' count the same as a single SQL 'Seat License'?
>
> Presumably a Session can contain multiple Threads, when hooked up to a
> web-application like Witango?
>
> Although I primarily use SQL-Server for all my work, I freely admit to being
> more of an Application Programmer than a Database Programmer. Once upon a
> time I used to have a DBA on staff - but down-sizing has left me as the sole
> Developer here to cover all the bases.
>
> I know we've covered this topic in the past, but I could use a refresher if
> anyone has some comments.
>
> Thank you. Cheers...
>
> Scott Cadillac
> http://xml-extra.net
> [EMAIL PROTECTED]
>
> ----- Original Message -----
> From: "Klaus Sonnenleiter" <[EMAIL PROTECTED]>
> To: "Multiple recipients of list witango-talk" <[EMAIL PROTECTED]>
> Sent: Tuesday, April 30, 2002 6:29 PM
> Subject: Re: Witango-Talk: Identity after Insert
>
> > Brad,
> >
> > Sounds like you're using either MS-SQL or Sybase. Either way you don't
> have
> > to run a select after the insert. Both have a built-in variable for
> > auto-increment fields (@@identity) and you can either return it in your
> > stored procedure or from the next Tango action.
> >
> > The only problem with doing this in a Tango action is that there is no
> real
> > guarantee that you're seeing the correct identity field. For example, if
> you
> > are using two consecutive Tango actions, you could run into a situation
> where
> > two threads are doing an insert and the first one gets the identity field
> > only after the second insert - guess what you're getting for @@identity?
> > Yep... ;-) Remember, this has nothing to do with Tango - there is only one
> > identity variable and it will always be set ot the last identity field
> > inserted.
> >
> > Klaus
> >
> > On Tuesday 30 April 2002 08:37, Brad Robertson wrote:
> > > I am currently using a Direct DBMS action in wTango that calls a stored
> > > procedure to insert a record and then return the new identity of that
> > > field, it seems that works better than a insert then search action to
> get
> > > the identity field. I was wondering if 5.0 or later will have the
> ability
> > > to grab this field on insert, or if anybody has a less tedious way of
> doing
> > > this...
> > >
> > > Brad
> >
> > ----------------------------------------
> > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> > Content-Transfer-Encoding: quoted-printable
> > Content-Description:
> > ----------------------------------------
> > ________________________________________________________________________
> > TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> > with unsubscribe witango-talk in the message body
> >
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
________________________________________________________________________
TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
with unsubscribe witango-talk in the message body
