Identity column value increments by 2 rather than 1 on insert

SQL Service Broker

SIS


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.

Top

Re: Identity column value increments by 2 rather than 1 on insert

Remus Rusanu


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

Can you post the code of your procedure






Top

Re: Identity column value increments by 2 rather than 1 on insert

SIS

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]



Top

Re: Identity column value increments by 2 rather than 1 on insert

Remus Rusanu

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.





Top