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
SETNOCOUNTON
GO
IFNOTEXISTS
(SELECT*FROMsys.databases
WHEREname='AdventureWorks'
AND is_broker_enabled= 1)
BEGIN
ALTERDATABASE AdventureWorksSET ENABLE_BROKER
END
GO
USE AdventureWorks
GO
IFEXISTS(SELECT*
FROMsys.services
WHEREname='InitiatorService')
BEGIN
DROPSERVICE InitiatorService
END
GO
IFEXISTS(SELECT*
FROMsys.services
WHEREname='TargetService')
BEGIN
DROPSERVICE TargetService
END
GO
IFEXISTS(SELECT*
FROMsys.service_contracts
WHEREname='HelloWorldContract')
BEGIN
DROPCONTRACT HelloWorldContract
END
GO
IFEXISTS(SELECT*
FROMsys.service_message_types
WHEREname='HelloWorldMessage')
BEGIN
DROPMESSAGETYPE HelloWorldMessage
END
GO
IFOBJECT_ID('[dbo].[InitiatorQueue]')ISNOTNULLAND
EXISTS(SELECT*
FROMsys.objects
WHEREobject_id=OBJECT_ID('[dbo].[InitiatorQueue]')
ANDtype='SQ')
BEGIN
DROPQUEUE [dbo].[InitiatorQueue]
END
GO
IFOBJECT_ID('[dbo].[TargetQueue]')ISNOTNULLAND
EXISTS(SELECT*
FROMsys.objects
WHEREobject_id=OBJECT_ID('[dbo].[TargetQueue]')
ANDtype='SQ')
BEGIN
DROPQUEUE [dbo].[TargetQueue]
END
GO
CREATEMESSAGETYPE HelloWorldMessage
VALIDATION= WELL_FORMED_XML
GO
CREATECONTRACT HelloWorldContract
( HelloWorldMessage SENTBY INITIATOR)
GO
CREATEQUEUE [dbo].[TargetQueue]
GO
CREATEQUEUE [dbo].[InitiatorQueue]
GO
CREATESERVICE InitiatorService
ONQUEUE [dbo].[InitiatorQueue]
GO
CREATESERVICE TargetService
ONQUEUE [dbo].[TargetQueue]
(HelloWorldContract)
GO
-- Starting conversation
USE AdventureWorks
GO
--BEGIN TRANSACTION
GO
DECLARE @.messageXML
SET @.message= N'<message>Hello, World!</message>'
-- Declare a variable to hold the conversation
-- handle.
DECLARE @.conversationHandleUNIQUEIDENTIFIER
-- Begin the dialog.
BEGINDIALOGCONVERSATION @.conversationHandle
FROMSERVICE InitiatorService
TOSERVICE'TargetService'
ONCONTRACT HelloWorldContract;
-- Send the message on the dialog.
SENDONCONVERSATION @.conversationHandle
MESSAGETYPE 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