Some months ago I was playing with Service Broker and everything was ok. But now I just can't get it work. I opened a sample "helloworld" project and still cannot get a message in a target queue. No errors, just empty queues. We have two SQL servers sept CTP on different computers and both give the same issue. It looks like it should have a very simple solution that I cannot come up with.
The following script initializes service broker and sends a message, then selects both target and initiator queues. On my environment it just returns two empty selects.
--Initializing service broker objects
use master
GO
SET NOCOUNT ON
GO
IF NOT EXISTS
(SELECT * FROM sys.databases
WHERE name = 'AdventureWorks'
AND is_broker_enabled = 1)
BEGIN
ALTER DATABASE AdventureWorks SET ENABLE_BROKER
END
GO
USE AdventureWorks
GO
IF EXISTS (SELECT *
FROM sys.services
WHERE name = 'InitiatorService')
BEGIN
DROP SERVICE InitiatorService
END
GO
IF EXISTS (SELECT *
FROM sys.services
WHERE name = 'TargetService')
BEGIN
DROP SERVICE TargetService
END
GO
IF EXISTS (SELECT *
FROM sys.service_contracts
WHERE name = 'HelloWorldContract')
BEGIN
DROP CONTRACT HelloWorldContract
END
GO
IF EXISTS (SELECT *
FROM sys.service_message_types
WHERE name = 'HelloWorldMessage')
BEGIN
DROP MESSAGE TYPE HelloWorldMessage
END
GO
IF OBJECT_ID('[dbo].[InitiatorQueue]') IS NOT NULL AND
EXISTS(SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('[dbo].[InitiatorQueue]')
AND type = 'SQ')
BEGIN
DROP QUEUE [dbo].[InitiatorQueue]
END
GO
IF OBJECT_ID('[dbo].[TargetQueue]') IS NOT NULL AND
EXISTS(SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('[dbo].[TargetQueue]')
AND type = 'SQ')
BEGIN
DROP QUEUE [dbo].[TargetQueue]
END
GO
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR)
GO
CREATE QUEUE [dbo].[TargetQueue]
GO
CREATE QUEUE [dbo].[InitiatorQueue]
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue]
GO
CREATE SERVICE TargetService
ON QUEUE [dbo].[TargetQueue]
(HelloWorldContract)
GO
-- Starting conversation
USE AdventureWorks
GO
--BEGIN TRANSACTION
GO
DECLARE @message XML
SET @message = N'<message>Hello, World!</message>'
-- Declare a variable to hold the conversation
-- handle.
DECLARE @conversationHandle UNIQUEIDENTIFIER
-- Begin the dialog.
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE InitiatorService
TO SERVICE 'TargetService'
ON CONTRACT HelloWorldContract;
-- Send the message on the dialog.
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloWorldMessage
(@message)
print @conversationHandle
GO
--COMMIT TRANSACTION
GO
SELECT * FROM [dbo].[TargetQueue]
SELECT * FROM [dbo].[InitiatorQueue]
Cannot get the Service Broker work
FranklinIssac
Ken Hwang
I have "WITH ENCRYPTION = OFF" and it's still not working. The status in the transmission queue reads:
An exception occurred
while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.Any idea
Thx
nuisance
WITH ENCRYPTION = OFF
to BEGIN DIALOG CONVERSATION statement and everything started working as expected (thanks Tito Jermaine for comment).
Osi
Niels
nuclearfusion
Can you run this query in AdventureWorks:
SELECT * FROM sys.transmission_queue
and look at the content of the transmission_status column It should indicate the reason for which the message cannot be delivered.
HTH,
~ Remus
Jody Byrd
The original Windows user that created the database is no longer available. Change the database ownership to a valid login, so that user 'dbo' can be impersonated:
ALTER AUTHORIZATION ON DATABASE::[yourdatabase] TO [sa];
HTH,
~ Remus