October 1, 2005 at 5:05 am
Hello, great article, compact & clear. On my june CTP version, it didn't seem to work without the following change (maybe update the article ? )
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract WITH ENCRYPTION=OFF, LIFETIME= 600;
-- Send a message on the conversation
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloMessage (@message)
COMMIT TRANSACTION
END
GO
regards,
steph
October 17, 2005 at 8:21 am
Hello
I too am having problems getting this sample to work.
I created a db called TestDb.
I then pasted the sample service broker code (with your ENCRYPTION change)
into the mgmt studio. I modified the USE statement at the top.
There is never anything in the ReceiverQueue.
Everyone tries their sample code against the AdventureWorks db. I suspect that
db has some property set that we are not being told about.
Could you try the sample code after you create a new database.
Thanks.
March 10, 2006 at 7:31 am
I tried your example but could not able to receive the message back. Pelase guide what I am doing wrong. Receive statement does not return any data back.
USE master;
GO
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ) ;
Go
Use MySampleDB
Go
Create MESSAGE TYPE HelloMessage VALIDATION = None
GO
Create CONTRACT HelloContract ( HelloMessage SENT BY INITIATOR )
GO
Create Queue SenderQueue
GO
Create QUEUE ReceiverQueue
GO
Create Service Sender ON QUEUE SenderQueue
GO
Create Service Receiver ON QUEUE ReceiverQueue (HelloContract)
GO
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message nvarchar(100)
Begin
Begin Transaction;
Begin Dialog @conversationHandle
From Service Sender
TO Service 'Receiver'
ON CONTRACT HelloContract
Set @Message = N'Hellow, World';
Send ON Conversation @conversationHandle MESSAGE TYPE HelloMessage (@message)
Commit Transaction
End
Go
Receive Convert(Nvarchar(max),message_body) as message
From ReceiverQueue
GO
Select * from SenderQueue
Select * from ReceiverQueue
Select * from dbo.ServiceBrokerQueue
March 19, 2006 at 1:28 pm
hbatra -
I have just developed a C# app that does exactly what you explained (ie, writes from an MSMQ queue to a SQL Server table). Do you mind sharing how you run this app? As a scheduled task, a job in SQL Server, a service??? Just wondering. Thanks.
Steve
April 17, 2006 at 11:43 am
Nice article, but I'm not getting anything when I execute posted code.
Does anybody have an idea why?
Thanks
June 18, 2006 at 5:21 pm
I couldn't get any result too.
Anyway, thanks for sharing your exp.
Patrick
July 14, 2006 at 8:38 am
I was not getting any results either in a new database until I ran this script to enable the serice broker in my database:
ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER
from 2005 books online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ac7e4c7c-e52f-4883-8f3c-9336cc77a9c8.htm
hope that helps....
July 14, 2006 at 8:59 am
It is enabled in my database, and still nothing.
July 25, 2006 at 10:54 am
Here are the steps that worked (finally) for me:
If not, do a select * from sys.transmission_queue to see what errors were generated.
Hope that helps....
August 23, 2006 at 11:11 am
I finally got this to work. How do I get rid of messages in sys.transmission_queue?
December 14, 2007 at 2:04 am
Great article, but something went wrong when I ran the sample code - it returns an empty message.
??
BR
Peter Pirker
February 1, 2008 at 6:11 am
Hi Srinivas...this was an excellent pice of code... can I pls. have your mail id?? I've some further queries to be clarified..my mail ids are : kbagchi@careindia.org & kingshukbagchi@rediffmail.com .
Thnaks is advance,
Kingshuk.
November 18, 2008 at 12:56 pm
Here are the steps that worked (finally) for me:
Login as 'sa' on your local sql 2005 server.
Create a new database
In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)
Copy and paste script from 'Intro...to Service Broker' article into a new query window.
Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.
Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])
Run entire script on your newly created database and it should return the 'Hello world' message.
If not, do a select * from sys.transmission_queue to see what errors were generated.
Hope that helps....
I ran the following:
ALTER DATABASE AdventureWorks SET ENABLE_BROKER
Then, after running the following, I still get no results:
-- We will use adventure works as the sample database
USE AdventureWorks
GO
-- First, we need to create a message type. Note that our message type is
-- very simple and allowed any type of content
CREATE MESSAGE TYPE HelloMessage
VALIDATION = NONE
GO
-- Once the message type has been created, we need to create a contract
-- that specifies who can send what types of messages
CREATE CONTRACT HelloContract
(HelloMessage SENT BY INITIATOR)
GO
-- The communication is between two endpoints. Thus, we need two queues to
-- hold messages
CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO
-- Create the required services and bind them to be above created queues
CREATE SERVICE Sender
ON QUEUE SenderQueue
CREATE SERVICE Receiver
ON QUEUE ReceiverQueue (HelloContract)
GO
-- At this point, we can begin the conversation between the two services by
-- sending messages
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
WITH ENCRYPTION=OFF, LIFETIME= 600;
-- Send a message on the conversation
SET @message = N'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloMessage (@message)
COMMIT TRANSACTION
END
GO
-- Receive a message from the queue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue;
-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 18, 2008 at 12:59 pm
Here are the steps that worked (finally) for me:
Login as 'sa' on your local sql 2005 server.
Create a new database
In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)
Copy and paste script from 'Intro...to Service Broker' article into a new query window.
Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.
Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with BEGIN DIALOG @conversationHandle ....ON CONTRACT HelloContract [here])
Run entire script on your newly created database and it should return the 'Hello world' message.
If not, do a select * from sys.transmission_queue to see what errors were generated.
Hope that helps....
Thanks, it does work on a new db, but why does it not work on AdventureWorks, even after enabling the Service Broker??
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 19, 2008 at 9:59 pm
This has actually worked for me... have you done "alter database [database name] set enable_broker"?
Kingshuk
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply