Service Broker Problem (2)

External Since: Jan 10, 2008 Posts: 40

I have two Databases set up: QueueTest1 and QueueTest2.

In QueueTest1 I have a servicebroker queue set up. This queue has activation
on it, which calls the stored procedure, QueueTest1.dbo.ProcessQueue.
ProcessQueue calls a stored procedure in QueueTest2, which writes an entry to
a table in QueueTest2, QueueTest2.dbo.MessageLog.

When I remove activation from the queue and call ProcessQueue manually, then
the entry is written to the table in QueueTest2. If, however, I have
activation on the queue then when I add a message to the queue then the queue
shuts down.

If I have ProcessQueue only writing to a table in QueueTest1, then the queue
works from start to finish. i.e I put a message in the queue, it is popped
off automatically, and the entry is written to a table. It seems, therefore,
that there may be a permission problem that kicks in when the queue on one
database tries to write to a table on another database.

I have added a script below to recreate the problem I'm getting. Please run
the script. After you have run the script, please run SuccessfulTest and then
UnsuccessfulTest.

What I was expecting:
There would be a row in both QueueTest1..MessageLog and
QueueTest2..MessageLog to show that both SPs were successful.

But what I actually got was:
A row in QueueTest1..MessageLog, but no row in QueueTest2..MessageLog. And
the queue called TestQueue has shut down. This shows that UnsuccessfulTest
proc failed.

<SCRIPT>

create database QueueTest1
go
create database QueueTest2
go

use QueueTest2
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MessageLog](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[Msg] [varchar](max) NOT NULL,
[LogTime] [datetime] NOT NULL CONSTRAINT [DF_Log_LogTime] DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO
create proc [dbo].[LogMessage] @Msg varchar(max)
as
insert into MessageLog(Msg) values (@Msg)

GO

use QueueTest1
go

CREATE TABLE [dbo].[MessageLog](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[Msg] [varchar](max) NOT NULL,
[LogTime] [datetime] NOT NULL CONSTRAINT [DF_Log_LogTime] DEFAULT (getdate())
) ON [PRIMARY]

go
alter database QueueTest1 set enable_broker with rollback immediate
go

CREATE MESSAGE TYPE TestMessage
VALIDATION = NONE
CREATE CONTRACT TestContract
(TestMessage SENT BY INITIATOR)
CREATE QUEUE TestQueue
CREATE SERVICE TestService
ON QUEUE TestQueue (TestContract)
go

CREATE PROCEDURE [dbo].[ProcessQueueInOtherDatabase]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @h UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @payload varbinary(max);

WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP(1)
@h = conversation_handle,
@messageTypeName = message_type_name,
@payload = message_body
FROM TestQueue), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
PRINT @MessageTypeName
IF N'TestMessage' = @messageTypeName
BEGIN
declare @Msg varchar(max)
set @Msg = 'Message number ' + convert(varchar,cast(@payload as int))
insert into QueueTest2..MessageLog(Msg) values (@Msg)
END
ELSE IF
N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' =
@messageTypeName
BEGIN
END CONVERSATION @h;
END
ELSE IF N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
= @messageTypeName
BEGIN
-- Log the received error into ERRORLOG and system Event
Log (eventvwr.exe)
DECLARE @h_string NVARCHAR(100);
DECLARE @error_message NVARCHAR(4000);
SELECT @h_string = CAST(@h AS NVARCHAR(100)),
@error_message = CAST(@payload AS NVARCHAR(4000));
RAISERROR (N'Conversation %s was ended with error %s', 10,
1, @h_string, @error_message) WITH LOG;
END CONVERSATION @h;
END

COMMIT;
END
END
go

CREATE PROCEDURE [dbo].[ProcessQueueInThisDatabase]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @h UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @payload varbinary(max);

WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP(1)
@h = conversation_handle,
@messageTypeName = message_type_name,
@payload = message_body
FROM TestQueue), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
PRINT @MessageTypeName
IF N'TestMessage' = @messageTypeName
BEGIN
declare @Msg varchar(max)
set @Msg = 'Message number ' + convert(varchar,cast(@payload as int))
insert into MessageLog(Msg) values (@Msg)
END
ELSE IF
N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' =
@messageTypeName
BEGIN
END CONVERSATION @h;
END
ELSE IF N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
= @messageTypeName
BEGIN
-- Log the received error into ERRORLOG and system Event
Log (eventvwr.exe)
DECLARE @h_string NVARCHAR(100);
DECLARE @error_message NVARCHAR(4000);
SELECT @h_string = CAST(@h AS NVARCHAR(100)),
@error_message = CAST(@payload AS NVARCHAR(4000));
RAISERROR (N'Conversation %s was ended with error %s', 10,
1, @h_string, @error_message) WITH LOG;
END CONVERSATION @h;
END

COMMIT;
END
end
go

/*
This Stored Procedure sets the activation SP for the Queue to the one that
writes an entry to a table in another database,
and then puts a message into the queue.
It causes the queue to fall over.

Give it a few seconds and then select * from QueueTest2..MessageLog
*/

create procedure UnsuccessfulTest
as

ALTER QUEUE TestQueue with STATUS=on,
ACTIVATION (
STATUS = ON, -- Turn on internal activation
PROCEDURE_NAME = [ProcessQueueInOtherDatabase], -- Our stored proc
MAX_QUEUE_READERS = 1, -- Up to 4 concurrent readers
EXECUTE AS SELF) -- Execute as user of incoming dialog

DECLARE @Dialog uniqueidentifier
DECLARE @Message int
BEGIN DIALOG CONVERSATION @Dialog
FROM SERVICE TestService
TO SERVICE 'TestService'
ON CONTRACT TestContract
WITH ENCRYPTION = OFF
SET @Message = 10;
SEND ON CONVERSATION @Dialog
MESSAGE TYPE TestMessage (@Message)

go

/*
This Stored Procedure sets the activation SP for the Queue to the one that
writes an entry to a table in this database,
and then puts a message into the queue.
It works properly - the message is logged in the MessageLog table.

Give it a few seconds and then select * from MessageLog

*/
create procedure SuccessfulTest
as

ALTER QUEUE TestQueue with STATUS=on,
ACTIVATION (
STATUS = ON, -- Turn on internal activation
PROCEDURE_NAME = [ProcessQueueInThisDatabase], -- Our stored proc
MAX_QUEUE_READERS = 1, -- Up to 4 concurrent readers
EXECUTE AS SELF) -- Execute as user of incoming dialog

DECLARE @Dialog uniqueidentifier
DECLARE @Message int
BEGIN DIALOG CONVERSATION @Dialog
FROM SERVICE TestService
TO SERVICE 'TestService'
ON CONTRACT TestContract
WITH ENCRYPTION = OFF
SET @Message = 10;
SEND ON CONVERSATION @Dialog
MESSAGE TYPE TestMessage (@Message)

go

</SCRIPT>

External Since: Jan 11, 2008 Posts: 152

Hi,
According to your detailed script, I reproduced your issue at my side. From
my research, the stored procedure [ProcessQueueInThisDatabase] actually was
not executed at all. I add some print statement to the begining of the
stored procedure, however nothing was printed.

I will try to consult the product team on this issue and get back to you as
soon as possible. I also recommend that you leave me
(changliw_at_microsoft_dot_com) an email response so that I can timely
update you.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================