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]
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
. I just included WITH ENCRYPTION = OFF
to BEGIN DIALOG CONVERSATION statement and everything started working as expected (thanks Tito Jermaine for comment).|||The other way around it is to mark your target database as trustworthy: alter database db_name set trustworthy on, and create a master key in both databases: create master key encryption by password = 'some_pwd'
Niels
|||
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
|||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
|||For those of you reading this and asking what statement the "WITH ENCRYPTION = OFF" clause applies to, it's the BEGIN DIALOG CONVERSATION statement.
No comments:
Post a Comment