Showing posts with label increments. Show all posts
Showing posts with label increments. Show all posts

Wednesday, March 7, 2012

Identity column value increments by 2 rather than 1 on insert

I have an issue with a stored procedure activation on a service broker queue. The activation stored procedure simply RECEIVES the top message and then INSERTs a row into a table with an identity INT column. Each row inserted has the identity column value incremented by 2 rather than 1. Only one row is inserted in the table.

If the activation is set to OFF and then manually calling the original stored procedure the insert works fine and the identity column value is only incremented by one.

Do you have any suggestions on why the identity column value increments by 2?

Thanks.

I tested what you describe and the identities are implemented by one, as expected.

Can you post the code of your procedure?

|||

Here are some simple test scripts to create the service broker logic:

-- Configure Queue

CREATE MESSAGE TYPE TestMessage VALIDATION = NONE

CREATE CONTRACT TestContract (TestMessage SENT BY INITIATOR)

CREATE QUEUE QueueTest WITH STATUS = ON, ACTIVATION ( PROCEDURE_NAME = PTest,

MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' )

CREATE SERVICE ServiceTest ON QUEUE QueueTest ( TestContract )

-- Send procedure

CREATE PROCEDURE [dbo].[PTestLogMessage]

@.msg_details varchar(390)

AS

BEGIN

DECLARE @.handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.handle FROM SERVICE ServiceTest TO

SERVICE 'ServiceTest' ON CONTRACT TestContract WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.handle MESSAGE TYPE TestMessage ( @.msg_details )

END

-- Activation procedure

CREATE PROCEDURE [dbo].[PTest]

AS

DECLARE @.msg VARCHAR(390)

BEGIN

RECEIVE TOP(1) @.msg = message_body

FROM dbo.QueueTest

-- This insert results in the identity column value incrementing by 2

INSERT INTO t_test ( msg )

VALUES (@.msg)

END

-- table

CREATE TABLE [dbo].[t_test]([id] [int] IDENTITY(1,1) NOT NULL,

[msg] [varchar](390) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_t_test] PRIMARY KEY CLUSTERED

([id] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

|||

Your activated procedure is not guaranteed to RECEIVE a message each time it's activated. In other words, you should expect that RECEIVE returns an empty rowset. In fact, if you do no loop inside your procedure until you hit an empty resultset (hit 'bottom' of queue), the activation launcher will loop for you, and call the procedure again. So the activated procedure code you showed is pretty much guaranteed to be called twice in a row for each message enqueuef, once because it was activated and once because the activation did not see that you hit an empty RECEIVE. The seconf time you will hit one. Because you do not check whether the RECEIVE returned or not an empty rowset (@.@.ROWCOUNT > 0) you insert twice for each message, so the identity is incremented by two.

Also you should project into RECEIVE the conversation_handle and message_type_name and repsond properly to 'EndDialog' and 'Error' messages, and the conversations have to be eventually ended, otherwise the sys.conversation_endpoints will grow out of control.

Sunday, February 19, 2012

identity and identity increment

Hello,
whenever I add a record to a table with identity column it increments by one
which is what I want. But when I input a record that was reject for any
reason (I.e. primary key violation) and then input another record it will
not be increment by one...it skips the number that would of been assigned
to the incorrect record. is there an easy to ensure that it always
increments by one?> whenever I add a record to a table with identity column it increments by
> one which is what I want. But when I input a record that was reject for
> any reason (I.e. primary key violation) and then input another record it
> will not be increment by one...it skips the number that would of been
> assigned to the incorrect record. is there an easy to ensure that it
> always increments by one?
No, if you are going to use IDENTITY, you should learn to live with gaps, or
else use something more like a sequence generator (essentially you lock the
entire table and use MAX(id_col)+1). If you delete any row other than the
most recent, how do you account for that gap? Do you intend to re-insert a
row with ID = 40 when in fact your next ID should have been 722? Are you
associatng some tangible value to the actual ID value generated? If so,
why? In reality, the consumers of the data should not really care if thir
next row gets an ID of 36 or 37 or 38.
http://www.aspfaq.com/2523
A|||That is the nature of using IDENTITY; transactions that are rolled back caus
e
gaps in the sequence. It's one of the reasons some DBA's won't use IDENTITY.
The only way around it is to use a custom id sequence function. Itzik
Ben-Gan wrote an article for SQL Server Magazine a few months back on how to
create custom identity functions.
HTH,
-Mark Williams
"John Smith" wrote:

> Hello,
> whenever I add a record to a table with identity column it increments by o
ne
> which is what I want. But when I input a record that was reject for any
> reason (I.e. primary key violation) and then input another record it will
> not be increment by one...it skips the number that would of been assigned
> to the incorrect record. is there an easy to ensure that it always
> increments by one?
>
>|||thanks... that helps
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCh7ueQeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> No, if you are going to use IDENTITY, you should learn to live with gaps,
> or else use something more like a sequence generator (essentially you lock
> the entire table and use MAX(id_col)+1). If you delete any row other than
> the most recent, how do you account for that gap? Do you intend to
> re-insert a row with ID = 40 when in fact your next ID should have been
> 722? Are you associatng some tangible value to the actual ID value
> generated? If so, why? In reality, the consumers of the data should not
> really care if thir next row gets an ID of 36 or 37 or 38.
> http://www.aspfaq.com/2523
> A
>