Re: Sequence numbering
- From: "Michel Prévost" <michel.prevost@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 11:36:23 -0500
Could you try using a column with a IDENTITY column. Your problem is because
many threads accessing the sproc at the same time will get the same ID.
at the same time, 2 or more threads execute the SELECT and get the same ID,
because the UPDATE following it has not been called.
With an IDENTITY column, you could do this:
INSERT INTO [EDI].[Common].[TP_Sequence]
.......
select @@identity as Sequence
"Tom" <fishingrod45@xxxxxxxxxxx> wrote in message
news:1168015048.583819.304410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
We have a stored procedure that is called when a unique sequence number
is required, it is pasted below. The SP is called within a map, on
each ClaimHeader record written. When multiple orchestrations are
initiated, sometimes there are resulting records that share the
'unique' id. [That is the problem.] For example, if 113 records
execute the SP, 108 of them are unique and 5 records share the same
'unique' id. Is there a better way to control sequencing within a map
on records inserted via the SQL adapter? Yes we do use the
autoincrement field so we are able to get the records inserted,
regardless, but we want the unique id to be unique. We believe that a
possible cause may be that several SP commands may be blending into one
(?) as multiple instances of the orchestration are blasting through
biztalk and SQL server is unable to distinguish the SP execute commands
as separate.
The reason the unique id is needed is to retain history on same
ClaimNumbers within the DB, but each -inbound- file will contain unique
ClaimNumbers, so as long as each claim is assigned the 'unique' id the
different versions of each ClaimNumber can be distinguished. This
didn't become a problem until we de-batched our inbound claims file.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SELECT_Sequence_from_TP_Sequence]
-- @SendID|@RecID|@Trans
(@SendID varchar(20),
@RecID varchar(20),
@Trans Varchar(4)
)
AS
declare @Seq BigInt
SELECT @Seq = Sequence from [EDI].[Common].[TP_Sequence]
where
([SenderID] = @SendID and [ReceiverID] = @RecID and [Transaction] =
@Trans)
if @Seq > 0 begin
UPDATE [EDI].[Common].[TP_Sequence]
SET
[Sequence] = @Seq + 1
where
([SenderID] = @SendID and [ReceiverID] = @RecID and [Transaction] =
@Trans)
end else begin
-- Here is a new Send/Rec ID pair, insert it
insert into [EDI].[Common].[TP_Sequence]
([SenderID]
,[ReceiverID]
,[Transaction]
,[Sequence])
VALUES
(@SendID, @RecID, @Trans, 1
)
set @Seq = 1
Select @Seq as Sequence
end
Select @Seq as Sequence
Thanks,
Tom
.
- References:
- Sequence numbering
- From: Tom
- Sequence numbering
- Prev by Date: RE: Forcing Biztalk to be something it's not?
- Next by Date: Re: BizTalk Server maximum Configuration
- Previous by thread: Re: Sequence numbering
- Next by thread: Accessing Error Properties Created w/ Failed Message Routing
- Index(es):
Relevant Pages
|
