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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Date: Tue, 14 Dec 2004 21:28:14 -0500

Dan, I'm real curious myself... ;-) 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com
.
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message 
news:eGb4VYk4EHA.3504@TK2MSFTNGP12.phx.gbl...
> Note - I've also set NOCOUNT ON.
Betcha that took care of the problem :-)
-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eRhBFth4EHA.2180@TK2MSFTNGP12.phx.gbl...
> Bizarre, huh?  Let's try eliminating the transaction - it's not giving you
> anything anyway.  Also, let's store the value of SCOPE_IDENTITY() in a
> variable:
>
> 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
> SET NOCOUNT ON
>
> DECLARE @ID int
>
> -- Insert Transaction summary 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)
>
> SET @ID = SCOPE_IDENTITY()
>
> RETURN @ID
> GO
>
> Note - I've also set NOCOUNT ON.
>
> -- 
> 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:u3CLumh4EHA.2428@TK2MSFTNGP14.phx.gbl...
> CREATE TABLE [TransHeader] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [tran_type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tran_source] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tran_dest] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tran_costcenter] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [tran_lot] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tran_begin] [datetime] NULL ,
> [tran_end] [datetime] NULL ,
> [tran_data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [tran_result] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [hermes_data] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_TransHeader] PRIMARY KEY  CLUSTERED
> (
>  [id]
> )  ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:ey$bxjh4EHA.924@TK2MSFTNGP14.phx.gbl...
>> 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