December 30, 2015 at 6:44 am
I have a problem with Service Broker.
I did any needed configuration to enable S.B. And sent message to Target. But Target is empty.
USE master
GO
IF DB_ID('Test_SSSB')>0
BEGIN
ALTER DATABASE Test_SSSB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Test_SSSB
END
GO
CREATE DATABASE Test_SSSB
GO
--------------------------------------------------------------------
USE Test_SSSB
GO
IF OBJECT_ID('EmployeeInfo')>0
DROP TABLE EmployeeInfo
GO
CREATE TABLE EmployeeInfo
(
EmployeeID INT PRIMARY KEY,
FirstNamre NVARCHAR(100),
LastName NVARCHAR(100)
)
GO
INSERT INTO EmployeeInfo (EmployeeID,FirstNamre,LastName) VALUES
(2034,N'Maria',N'Tahert')
GO
SELECT * FROM EmployeeInfo
GO
--------------------------------------------------------------------
ALTER DATABASE Test_SSSB SET ENABLE_BROKER with rollback immediate
GO
USE Test_SSSB
GO
--------------------------------------------------------------------
CREATE MESSAGE TYPE [//VVDB/MSGGrp/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [//VVDB/MSGGrp/ReplyMessage]
VALIDATION = WELL_FORMED_XML
GO
--------------------------------------------------------------------
CREATE CONTRACT [//VVDB/MSGGrp/SampleContract]
(
[//VVDB/MSGGrp/RequestMessage] SENT BY INITIATOR,
[//VVDB/MSGGrp/ReplyMessage] SENT BY TARGET
)
GO
--------------------------------------------------------------------
GO
CREATE QUEUE InitiatorQueue1DB
GO
CREATE SERVICE [//VVDB/MSGGrp/InitiatorService]
ON QUEUE InitiatorQueue1DB
GO
--------------------------------------------------------------------
CREATE QUEUE TargetQueue1DB
GO
CREATE SERVICE [//VVDB/MSGGrp/TargetService]
ON QUEUE TargetQueue1DB([//VVDB/MSGGrp/SampleContract])
GO
--------------------------------------------------------------------
DECLARE @InitDlghandle UNIQUEIDENTIFIER;
Declare @RequestMsg NVarchar(100);
Begin Transaction
Begin Dialog @InitDlghandle
From Service [//VVDB/MSGGrp/InitiatorService]
To service N'//VVDB/MSGGrp/TargetService'
On Contract [//VVDB/MSGGrp/SampleContract]
With Encryption = OFF;
Select @RequestMsg = N'<RequestMsg>Message for target service.</RequestMsg>';
SEND ON CONVERSATION @InitDlghandle
Message Type [//VVDB/MSGGrp/RequestMessage](@RequestMsg);
Select @RequestMsg As SentRequestMsg
Commit Transaction
Go
Finally I expected one record at the result of this Query. But It is empty .
Select * From TargetQueue1DB
Please Help me .
January 2, 2016 at 12:07 am
:doze:
January 3, 2016 at 12:54 pm
I copy/paste/ran your code and it ran fine for me. There was a message on the target queue.
If you use copy/paste from the code to a fresh database and try again, do you still not see any message on the queue? If you do this time, then go over the code you posted and the code you tried before and look for the differences. If it still does not work, then look here first: http://rusanu.com/2005/12/20/troubleshooting-dialogs/[/url].
January 10, 2016 at 4:00 am
I found the problem .
I login to ssms with my windows user.
And It hasn't access to do service brokers tasks in default.
Then I login with sa and every thing was true.
--------
And How did I found it ?
By this Query. (by default you can't see the service broker errors such as another errors)
Select transmission_status from Sys.transmission_queue
January 10, 2016 at 4:59 am
I am glad you found it!
Thanks for posting back - in case someone else runs into the same issue and finds this thread when googling for solutions, your post may be invaluable to them!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply