SQLServerCentral / Discuss Content Posted by Srinivas Sampath / Article Discussions / Article Discussions by Author / An Introduction to the Service Broker / Latest Posts

RE: An Introduction to the Service Broker

NAIROOZ NILAFDEEN — Tue, 05 Oct 2010 02:52:36 GMT

You dont need loop thru..instead u can use an activation sp.

RE: An Introduction to the Service Broker

S. Kusen — Tue, 14 Jul 2009 20:53:00 GMT

Seems like the code did not work for me at first either. as the earlier posts suggested, i enabled the service broker, and then created a master DB key for my DB. sys.transmission_queue had the following message:"The session keys for this conversation could not be created or accessed. The database master key is required for this operation."I then issued this:[code]CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''[/code]re-ran the code sample as presented in the article, and it worked.The WITH ENCRYPTION=OFF should have also worked as another poster suggested.Steve

RE: An Introduction to the Service Broker

jaiganesh_paramanantham — Wed, 25 Feb 2009 05:23:23 GMT

Excellent Article, I am new to service broker topic, I copied the sample script which you explained in your article. That is not working for me. I send a 'Hello world message. I can't see the message in the queue. Can you confirm is any set up needs to do before running this script?

RE: An Introduction to the Service Broker

Marios Philippopoulos — Thu, 20 Nov 2008 07:47:27 GMT

[quote][b]kingshukbagchi (11/19/2008)[/b][hr]This has actually worked for me... have you done "alter database [database name] set enable_broker"?Kingshuk[/quote]yes

RE: An Introduction to the Service Broker

kingshukbagchi — Wed, 19 Nov 2008 22:01:45 GMT

This has actually worked for me... have you done "alter database [database name] set enable_broker"?Kingshuk

RE: An Introduction to the Service Broker

kingshukbagchi — Wed, 19 Nov 2008 21:59:42 GMT

This has actually worked for me... have you done "alter database [database name] set enable_broker"?Kingshuk

RE: An Introduction to the Service Broker

Marios Philippopoulos — Tue, 18 Nov 2008 12:59:54 GMT

RE: An Introduction to the Service Broker

Marios Philippopoulos — Tue, 18 Nov 2008 12:56:40 GMT

[quote]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....[/quote]I ran the following:[quote]ALTER DATABASE AdventureWorks SET ENABLE_BROKER [/quote]Then, after running the following, I still get no results:[quote]-- We will use adventure works as the sample databaseUSE AdventureWorksGO-- First, we need to create a message type. Note that our message type is-- very simple and allowed any type of contentCREATE MESSAGE TYPE HelloMessageVALIDATION = NONEGO-- Once the message type has been created, we need to create a contract-- that specifies who can send what types of messagesCREATE CONTRACT HelloContract(HelloMessage SENT BY INITIATOR)GO-- The communication is between two endpoints. Thus, we need two queues to-- hold messagesCREATE QUEUE SenderQueueCREATE QUEUE ReceiverQueueGO-- Create the required services and bind them to be above created queuesCREATE SERVICE Sender ON QUEUE SenderQueueCREATE SERVICE Receiver ON QUEUE ReceiverQueue (HelloContract)GO-- At this point, we can begin the conversation between the two services by-- sending messages DECLARE @conversationHandle UNIQUEIDENTIFIERDECLARE @message NVARCHAR(100)BEGIN BEGIN TRANSACTION; BEGIN DIALOG @conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT HelloContract [b]WITH ENCRYPTION=OFF, LIFETIME= 600;[/b] -- Send a message on the conversation SET @message = N'Hello, World'; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE HelloMessage (@message) COMMIT TRANSACTIONENDGO-- Receive a message from the queueRECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue;-- CleanupDROP SERVICE SenderDROP SERVICE ReceiverDROP QUEUE SenderQueueDROP QUEUE ReceiverQueueDROP CONTRACT HelloContractDROP MESSAGE TYPE HelloMessageGO[/quote]

RE: An Introduction to the Service Broker

kingshukbagchi — Fri, 01 Feb 2008 06:11:54 GMT

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.

RE: An Introduction to the Service Broker

Peter Pirker — Fri, 14 Dec 2007 02:04:29 GMT

Great article, but something went wrong when I ran the sample code - it returns an empty message.??BRPeter Pirker

RE: An Introduction to the Service Broker

stevemc — Wed, 23 Aug 2006 11:11:00 GMT

I finally got this to work. How do I get rid of messages in sys.transmission_queue?

RE: An Introduction to the Service Broker

sql05guy — Tue, 25 Jul 2006 10:54:00 GMT

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....

RE: An Introduction to the Service Broker

espasojevic — Fri, 14 Jul 2006 08:59:00 GMT

It is enabled in my database, and still nothing.

RE: An Introduction to the Service Broker

sql05guy — Fri, 14 Jul 2006 08:38:00 GMT

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....

RE: An Introduction to the Service Broker

Patrick-304111 — Sun, 18 Jun 2006 17:21:00 GMT

I couldn't get any result too.

Anyway, thanks for sharing your exp.

Patrick

RE: An Introduction to the Service Broker

espasojevic — Mon, 17 Apr 2006 11:43:00 GMT

Nice article, but I'm not getting anything when I execute posted code.

Does anybody have an idea why?

Thanks

RE: An Introduction to the Service Broker

SQL-DBA — Sun, 19 Mar 2006 13:28:00 GMT

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

RE: An Introduction to the Service Broker

Shafiq Muhammad — Fri, 10 Mar 2006 07:31:00 GMT

Hi, 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

RE: An Introduction to the Service Broker

bobzigon — Mon, 17 Oct 2005 08:21:00 GMT

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.

RE: An Introduction to the Service Broker

steph-265477 — Sat, 01 Oct 2005 05:05:00 GMT

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 UNIQUEIDENTIFIERDECLARE @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 TRANSACTIONENDGO

regards,

steph

RE: An Introduction to the Service Broker

Srinivas Sampath — Thu, 01 Sep 2005 23:57:00 GMT

First check if the ReceiverQueue itself has any message. If not, check the sys.transmission_queue to see if any errors are logged. One other post that I made had a user with a similar problem and the sys.transmission_queue had a message indicating an error about a missing database master key. If you see a similar error, you can solve it using the method that I've outlined in: http://blogs.sqlxml.org/srinivassampath/archive/2005/06/19/3457.aspx

RE: An Introduction to the Service Broker

Kuido Külm — Wed, 31 Aug 2005 01:24:00 GMT

I test this script on JuneCTP2005 SQL2005, but RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueueorSELECT CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueuegives nothingMust the Sender and Receiver be different computers ?

RE: An Introduction to the Service Broker

Srinivas Sampath — Tue, 30 Aug 2005 11:52:00 GMT

First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.

Service Broker can basically be used to achieve asynchronous semantics in the database. In your case, the application tier receives messages from an external source and if you are not using transactional messaging in MSMQ, the performance of SSB and MSMQ is comparable. Also, in your case, if the application tier is load-balanced, you will have multiple Q's doing the job. I would not recommend replacing this solution with SSB because, if you need to do some pre-processing of the Q before executing the SP, then you cannot do it. Also, by using MSMQ in this case, you can have other applications that can also drain the Q.

SSB is largely for writing reliable, asynchronous, message oriented database applications.

RE: An Introduction to the Service Broker

Srinivas Sampath — Tue, 30 Aug 2005 11:46:00 GMT

First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.

Service Broker messages are always sent to a service. Each service is associated with a queue and each queue can have an activation procedure that wakes up when messages arrive at the queue. You can also specify the maximum number of activation procedures that Service Broker can instantiate for the queue.

RE: An Introduction to the Service Broker

Srinivas Sampath — Tue, 30 Aug 2005 11:43:00 GMT

First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.

Not sure if any commercial scale applications have yet been developed using Service Broker, but I remember Roger Walter writing a few articles about the same and also delivering a few webcasts.

Here is one article about some use cases: http://msdn.microsoft.com/sql/archive/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp

RE: An Introduction to the Service Broker

Srinivas Sampath — Tue, 30 Aug 2005 11:39:00 GMT

First off, I apologize for my late response to this thread. I did not have an account with SQL Server central and have just now created one.

Interesting question. I did think about this when I originally read about SSB and people do ask me this question when I deliver sessions on SSB. SSB sure comes close to MSMQ in several ways, but the first thing that I would consider before using SSB is: Do I want to do asynchronous operations in the database? If so, then SSB is for you. Second, SSB requires that both ends of your communication are SQL Server 2005. If this is the case, then again SSB is for you. Third, if you do require transactional message processing (and the above two points hold good), then SSB is for you, since transactional semantics are built in.

RE: An Introduction to the Service Broker

Srinivas Sampath — Tue, 30 Aug 2005 11:33:00 GMT

First off, I apologize for my late response to this thread. I did not have an account with SQL Server central and have just now created one.

To answer your question, the diagrams were created by me (just the brain wave that you get :-)). Sure you can use them.

RE: An Introduction to the Service Broker

Dale Corey — Thu, 28 Jul 2005 21:12:00 GMT

Srinivas Sampath,

I was very interested in your article as I am looking at Service Broker closely for my own personal research for an article. Where did you get your diagrams? Are they original? If not, well, you know the circumstances. If they are orignial, I would like to reference them in my next article. Of course, I will put your name on them.

Thanks a bunch!

RE: An Introduction to the Service Broker

NvrMind — Fri, 22 Jul 2005 11:11:00 GMT

One of the biggest limitation of Service Broker, when contrasted with MSMQ, is that Service Broker in its existing form in Sql 2005 works only when the two End Points are in Sql Server 2005, whereas MSMQ is wide open to any application having capability to write to the queues not just database applications.

I will clear my last point using the following example. I have an application (written in C++ many years ago), which receives real-time info from a vendor. Currently, I write the info to MSMQ so that no matter how fast info comes, I do not loose it, so I just put it on MSMQ queue. Next, I have another application, in C#, which picks up the message from MSMQ and executes a Stored Procedure on SQL Server, which writes to a table, which has triggers. So, this C# app can continue to process these messages at its own pace.

If Service Broker would have allowed interaction from external apps, I would let my C++ app to write directly to a Service Broker queue. Subsequently, the internal logic within the Service Broker would take the message from the Service Broker queue and process them in the same manner as explained above (execute SP, trigger etc.). This way I can get rid of my C# application and rely solely on Sql Service Broker queueing.

Can the above task/process be achieved by using just Service Broker and bypassing MSMQ completely?

On a different note, I know I can enhance my application by removing triggers and posting messages to Service Broker queue and then process them one by one by reading them from the queue, and processing them further as explained above. This will scale my application better.

But my more imperative need is to have as few in-between applications as possible (like the C# app) and let Service Broker do the work what my app is doing in C#.

Any suggestions, improvements which I can achieve in my approach?

Thanks

RE: An Introduction to the Service Broker

Adam Machanic — Thu, 30 Jun 2005 11:15:00 GMT

Nate:Look up WAITFOR in the SQL Server 2005 Books Online.

RE: An Introduction to the Service Broker

Ian Yates — Thu, 30 Jun 2005 07:31:00 GMT

Any options for some sort of event driven queue - ie, when a message is received, SQL will call a stored proc? Kind of like a trigger I suppose.

Sounds very much like MSMQ - I suppose it does make sense to store it in the DB. I'm yet to find the time to actually play with SQL 2005 Of the hundreds of articles I have read about it, this is the first to talk about the service broker - nice to know it exists!

RE: An Introduction to the Service Broker

N Cook — Thu, 30 Jun 2005 06:46:00 GMT

Thanks for this article. I am curious- how would you continually read the queue- Would you do so with an endless loop or a timer or something?

Thanks,Nate

RE: An Introduction to the Service Broker

philcart — Thu, 30 Jun 2005 02:26:00 GMT

Nice article.

However, I've yet to see a "real world" example for Service Broker, anyone got any links?

RE: An Introduction to the Service Broker

ckempste — Wed, 29 Jun 2005 22:41:00 GMT

Great article.

Any thoughts on this as a MSMQ replacement? especially with the release of MSMQ 3.0. I have yet to find a solid roadmap for MSMQ, this may be the next "big step" for MS.

An Introduction to the Service Broker

Srinivas Sampath — Wed, 29 Jun 2005 16:16:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/anintroductiontotheservicebroker.asp