Re: SP with RETURN @@IDENTITY returning 0??

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 15:10:04 -0500

It doesn't do anything with locking but the value returned should be fine.
Could you please post the exact DDL for the table - including all
constraints, indexes, etc.? 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rich Wallace" <rwallacejr@gmail.dontspamme.com> wrote in message
news:%236Pa%23Kh4EHA.3380@TK2MSFTNGP09.phx.gbl...
No triggers, I did try the SCOPE_IDENTITY and I have the same result.  Does
the SP not place the lock on the table as a straight ad-hoc INSERT would?
Doesn't make sense that it wouldn't, but I'm grasping at straws at the
moment.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:u39RXlg4EHA.824@TK2MSFTNGP11.phx.gbl...
> Is there a trigger on the TransHeader table?  Also, try using
> SCOPE_IDENTITY() instead of @@IDENTTY.
>
> -- 
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
>
> "Rich Wallace" <rwallacejr@gmail.dontspamme.com> wrote in message
> news:Of2mbjg4EHA.1300@TK2MSFTNGP14.phx.gbl...
> I have, same issue.
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:e$ox9hg4EHA.2404@TK2MSFTNGP14.phx.gbl...
> > Have you tried putting the RETURN outside of the transaction?
> >
> > -- 
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Rich Wallace" <rwallacejr@gmail.dontspamme.com> wrote in message
> > news:eXgDndg4EHA.2124@TK2MSFTNGP15.phx.gbl...
> > Hi all,
> >
> > The following SP is used in an external transaction logging solution
that
> > runs the INSERT into a 'header' table and returns the ID back to the
> caller
> > so that any 'detail' information is stored in a related table but links
to
> > teh 'header' table using the ID.
> >
> > CREATE PROCEDURE ins_tranheader
> >    @Tran_Type  varchar(20),
> >    @Tran_Source  varchar(35),
> >    @Tran_Dest  varchar(35),
> >    @Tran_Begin  datetime,
> >    @Tran_Comm  varchar(10),
> >    @Tran_Lot  varchar(4) = NULL,
> >    @Tran_Data  text
> > AS
> > BEGIN TRANSACTION
> > -- Insert Transaction sumary info into TranHeader table
> > INSERT TransHeader (
> >   tran_type,
> >   tran_source,
> >   tran_dest,
> >   tran_begin,
> >   tran_costcenter,
> >   tran_lot,
> >   tran_data)
> > VALUES (
> >   @Tran_Type,
> >   @Tran_Source,
> >   @Tran_Dest,
> >   @Tran_Begin,
> >   @Tran_Comm,
> >   @Tran_Lot,
> >   @Tran_Data)
> >
> > -- Return newly inserted Identity value for post processing end time
> update
> > RETURN @@IDENTITY
> >
> > COMMIT
> > GO
> >
> > There are time where the SP may be called simultaneously and I need to
> make
> > sure every call to the SP returns a valid @@IDENTITY, but sometimes, I
get
> a
> > 0 back.
> >
> > Any ideas??
> > TIA
> > -Rich
> >
> >
>
>

Relevant Pages