SQLServerCentral.com / Discuss Content Posted by Srinivas Sampath / Article Discussions by Author / Article Discussions / An Introduction to the Service Broker / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 10:45:37 GMT20RE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxYou dont need loop thru..instead u can use an activation sp.Tue, 05 Oct 2010 02:52:36 GMTNAIROOZ NILAFDEENRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxSeems 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 = '<password here>'[/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.SteveTue, 14 Jul 2009 20:53:00 GMTS. KusenRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxExcellent 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?Wed, 25 Feb 2009 05:23:23 GMTjaiganesh_paramananthamRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx[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]yesThu, 20 Nov 2008 07:47:27 GMTMarios PhilippopoulosRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxThis has actually worked for me... have you done "alter database [database name] set enable_broker"?KingshukWed, 19 Nov 2008 22:01:45 GMTkingshukbagchiRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxThis has actually worked for me... have you done "alter database [database name] set enable_broker"?KingshukWed, 19 Nov 2008 21:59:42 GMTkingshukbagchiRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx[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 &#119;indow.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]Thanks, it does work on a new db, but why does it not work on AdventureWorks, even after enabling the Service Broker??Tue, 18 Nov 2008 12:59:54 GMTMarios PhilippopoulosRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx[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 &#119;indow.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]Tue, 18 Nov 2008 12:56:40 GMTMarios PhilippopoulosRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxHi 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.Fri, 01 Feb 2008 06:11:54 GMTkingshukbagchiRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxGreat article, but something went wrong when I ran the sample code - it returns an empty message.??BRPeter PirkerFri, 14 Dec 2007 02:04:29 GMTPeter PirkerRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>I finally got this to work. How do I get rid of messages in sys.transmission_queue? </P>Wed, 23 Aug 2006 11:11:00 GMTstevemcRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Here are the steps that worked (finally) for me:</P><OL><LI>Login as 'sa' on your local sql 2005 server.</LI><LI>Create a new database </LI><LI>In a new query window, run this script: ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER (should return: Command(s) completed successfully.)</LI><LI>Copy and paste script from 'Intro...to Service Broker' article into a new query window.</LI><LI>Make sure you're in the newly created database and comment out 'USE AdentureWorks' line at the top.</LI><LI>Add this snippet (from a previous post) ... WITH ENCRYPTION=OFF, LIFETIME= 600; to the end of line #37. (the section that starts with <FONT color=#0000ff size=2>BEGIN</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>DIALOG</FONT><FONT size=2> @conversationHandle ....<FONT color=#0000ff size=2>ON</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>CONTRACT</FONT><FONT size=2> HelloContract [here])</FONT></FONT></LI><LI>Run entire script on your newly created database and it should return the 'Hello world' message.</LI></OL><P>If not, do a select * from sys.transmission_queue to see what errors were generated.</P><P>Hope that helps....</P>Tue, 25 Jul 2006 10:54:00 GMTsql05guyRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxIt is enabled in my database, and still nothing.Fri, 14 Jul 2006 08:59:00 GMTespasojevicRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>I was not getting any results either in a new database until I ran this script to enable the serice broker in my database:</P><P><FONT color=#008000 size=2><FONT color=#111111><STRONG>ALTER DATABASE [DatabaseNameHere] SET ENABLE_BROKER</STRONG></FONT><FONT color=#bb3333> </FONT></FONT></P><P><FONT color=#111111 size=2>from 2005 books online:</FONT></P><FONT color=#008000 size=2><FONT color=#111111 size=2><P>ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ac7e4c7c-e52f-4883-8f3c-9336cc77a9c8.htm</P><P>hope that helps....</P></FONT></FONT>Fri, 14 Jul 2006 08:38:00 GMTsql05guyRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>I couldn't get any result too.</P><P>Anyway, thanks for sharing your exp.</P><P>Patrick</P>Sun, 18 Jun 2006 17:21:00 GMTPatrick-304111RE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Nice article, but I'm not getting anything when I execute posted code.</P><P>Does anybody have an idea why?</P><P>Thanks</P>Mon, 17 Apr 2006 11:43:00 GMTespasojevicRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>hbatra -</P><P>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.</P><P>Steve</P>Sun, 19 Mar 2006 13:28:00 GMTSQL-DBARE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<DIV id=intelliTxt>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 <!--- IF THERE IS A SURVEY FOR THIS CATEGORY GET THE RESPONSES ---></DIV>Fri, 10 Mar 2006 07:31:00 GMTShafiq MuhammadRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Hello</P><P>I too am having problems getting this sample to work.</P><P>I created a db called TestDb.</P><P>I then pasted the sample service broker code (with your ENCRYPTION change)</P><P>into the mgmt studio. I modified the USE statement at the top.</P><P>There is never anything in the ReceiverQueue.</P><P>Everyone tries their sample code against the AdventureWorks db. I suspect that</P><P>db has some property set that we are not being told about.</P><P>Could you try the sample code after you create a new database.</P><P> </P><P>Thanks.</P>Mon, 17 Oct 2005 08:21:00 GMTbobzigonRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Hello, great article, compact &amp; clear. On my june CTP version, it didn't seem to work without the following change (maybe update the article ? )</P><P>DECLARE @conversationHandle UNIQUEIDENTIFIERDECLARE @message NVARCHAR(100)BEGIN BEGIN TRANSACTION; BEGIN DIALOG @conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT HelloContract <STRONG>WITH ENCRYPTION=OFF, LIFETIME= 600;</STRONG> -- Send a message on the conversation SET @message = N'Hello, World'; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE HelloMessage (@message) COMMIT TRANSACTIONENDGO</P><P>regards,</P><P>steph</P><P> </P>Sat, 01 Oct 2005 05:05:00 GMTsteph-265477RE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>First check if the ReceiverQueue itself has any message. If not, check the <STRONG>sys.transmission_queue</STRONG> to see if any errors are logged. One other post that I made had a user with a similar problem and the <STRONG>sys.transmission_queue</STRONG> 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: <A href="http://blogs.sqlxml.org/srinivassampath/archive/2005/06/19/3457.aspx">http://blogs.sqlxml.org/srinivassampath/archive/2005/06/19/3457.aspx</A></P>Thu, 01 Sep 2005 23:57:00 GMTSrinivas SampathRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxI 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 ?Wed, 31 Aug 2005 01:24:00 GMTKuido KülmRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.</P><P>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.</P><P>SSB is largely for writing reliable, asynchronous, message oriented <STRONG>database applications</STRONG>.</P>Tue, 30 Aug 2005 11:52:00 GMTSrinivas SampathRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.</P><P>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.</P>Tue, 30 Aug 2005 11:46:00 GMTSrinivas SampathRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.</P><P>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.</P><P>Here is one article about some use cases: <A href="http://msdn.microsoft.com/sql/archive/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp">http://msdn.microsoft.com/sql/archive/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp</A></P>Tue, 30 Aug 2005 11:43:00 GMTSrinivas SampathRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>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.</P><P>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.</P>Tue, 30 Aug 2005 11:39:00 GMTSrinivas SampathRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>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.</P><P>To answer your question, the diagrams were created by me (just the brain wave that you get :-)). Sure you can use them.</P>Tue, 30 Aug 2005 11:33:00 GMTSrinivas SampathRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Srinivas Sampath,</P><P>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.</P><P>Thanks a bunch! <img src='images/emotions/tongue.gif' height='20' width='20' border='0' title='Tongue' align='absmiddle'></P><P> </P>Thu, 28 Jul 2005 21:12:00 GMTDale CoreyRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>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.</P><P>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.</P><P>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.</P><P>Can the above task/process be achieved by using just Service Broker and bypassing MSMQ completely?</P><P>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.</P><P>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#.</P><P>Any suggestions, improvements which I can achieve in my approach?</P><P>Thanks</P>Fri, 22 Jul 2005 11:11:00 GMTNvrMindRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxNate:Look up WAITFOR in the SQL Server 2005 Books Online.Thu, 30 Jun 2005 11:15:00 GMTAdam MachanicRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>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.</P><P>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 <img src='images/emotions/sad.gif' height='20' width='20' border='0' title='Sad' align='absmiddle'> 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! <img src='images/emotions/biggrin.gif' height='20' width='20' border='0' title='Big Grin' align='absmiddle'></P>Thu, 30 Jun 2005 07:31:00 GMTIan YatesRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>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? </P><P>Thanks,Nate</P>Thu, 30 Jun 2005 06:46:00 GMTN CookRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Nice article.</P><P>However, I've yet to see a "real world" example for Service Broker, anyone got any links?</P><P> </P>Thu, 30 Jun 2005 02:26:00 GMTphilcartRE: An Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspx<P>Great article.</P><P>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. </P><P> </P>Wed, 29 Jun 2005 22:41:00 GMTckempsteAn Introduction to the Service Brokerhttp://www.sqlservercentral.com/Forums/Topic195536-213-1.aspxComments posted to this topic are about the content posted at <A HREF="http://www.sqlservercentral.com/columnists/sSampath/anintroductiontotheservicebroker.asp">http://www.sqlservercentral.com/columnists/sSampath/anintroductiontotheservicebroker.asp</A>Wed, 29 Jun 2005 16:16:00 GMTSrinivas Sampath