InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:20:55 GMT20http://www.sqlservercentral.com/Forums/Topic1455558-1281-1.aspxI have two service broker applications on one instance of database.One sends/recieve messages locally and other one sends/recieve messages to remote server.I wanted to disable the Remote service broker so that sender can no longer send messages to the remote queueI disabled the Queueue but I still see the Reciever is still getting messages and tables getting updated as a result.I want to stop the Remote Sender queue and I dont want transmission_queue to pile up.Wed, 22 May 2013 08:43:34 GMTWangcChiKaBastarhttp://www.sqlservercentral.com/Forums/Topic1448609-1281-1.aspxGuys,I have a Question about MAX_QUEUE_READERS option in Service Broker.In a high transaction environment where inserts/updates/deletes are very high, would it be affected byhaving MAX_QUEUE_READERS value higher than 1 ?What happens when a INSERT statement is issued and then Immediately DELETE is issued for the same recordand because of MAX_QUEUE_READERS being 2 or 3 ...DELETE gets processed before INSERT (Parallel Processing) ?OR I am not correct with my understanding of MAX_QUEUE_READERS ?I have a Production Issue where sometimes Queue backs up because of high User Activity and we have MAX_QUEUE_READERS set as 1.I am thinking of increasing it to may be 5, but would like to know ,that we don't end up with Orphan Records dueto multi-threaded parallel process created by MAX_QUEUE_READERS.Thanks.Wed, 01 May 2013 14:19:33 GMTWangcChiKaBastarhttp://www.sqlservercentral.com/Forums/Topic1434980-1281-1.aspxFire and Forget SolutionThe solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:How does this work if the initiator is part of a trigger. If it is changed to a SYNCHRONOUS setup. Does the trigger close after the send message is fired or does it have to wait for the full conversation cycle to complete. If so how can this be avoided in a synchronous setup.Mon, 25 Mar 2013 09:30:31 GMTBobby Gloverhttp://www.sqlservercentral.com/Forums/Topic1433854-1281-1.aspxHow many sprocs using begin or End conversation are needed in an ASYNC SB setup.I currently have 2 t the INITIATOR and the Target. As the Initiator ends the conversation first the Target gets no response to its end conversation.Do I need another procedure in the conversation so that the conversation will end properly and not leave endpoints/TOs in the Tempdb.Thu, 21 Mar 2013 09:15:34 GMTBobby Gloverhttp://www.sqlservercentral.com/Forums/Topic1432422-1281-1.aspxI'm trying to setup Database Mail and send a test message, but it's not working.I've done several step-by-steps and all and I can't get it to work. Also checked several posts in this forum but none helped. I think it used to work before (2 months ago) but we had to turn it off.I've enabled it in Surface Configuration, have tried recreating all profiles, restarted SQL Agent, checked version mismatch...I check the Database Mail and I get the following message:LogDatabase Mail (Database Mail Log)Log ID152Process ID7684Last Modified3/14/2013 6:49:58 PMLast Modified BySPEEDLING\sqlserviceMessage1) Exception Information===================Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseExceptionMessage: Mail configuration information could not be read from the database.Data: System.Collections.ListDictionaryInternalTargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)HelpLink: NULLSource: DatabaseMailEngineStackTrace Information=================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)2) Exception Information===================Exception Type: System.IndexOutOfRangeExceptionMessage: timeoutData: System.Collections.ListDictionaryInternalTargetSite: Int32 GetOrdinal(System.String)HelpLink: NULLSource: System.DataStackTrace Information=================== at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)Mon, 18 Mar 2013 18:20:15 GMTmstockhttp://www.sqlservercentral.com/Forums/Topic1221974-1281-1.aspxCan SSB be enable between databases on different instances on same domain/networkwithout the use of Certificates, Keys etc ?Wed, 14 Dec 2011 12:50:07 GMTWangcChiKaBastarhttp://www.sqlservercentral.com/Forums/Topic862366-1281-1.aspxI am trying to begin playing with the Service Broker, and I came across the following tutorial which basically does exactly what I needhttp://www.sql-server-performance.com/articles/dev/service_broker_p2.aspxhowever, I am not sure if there is a flaw in the tutorial or if I am missing somerthing. I have attached the code I am using. I am running on a SQL Server 2005 box and when I execute the code, I get the following error[b]Msg 9653, Level 16, State 1, Line 2The activation stored procedure 'spLogMessage' is invalid. Activation stored procedures cannot require parameters.[/b]but in the tutorial, the activation stored proc has parameters. Can someone please help me out? :)Thanks!MichaelTue, 09 Feb 2010 06:05:27 GMTSQL STEWIEhttp://www.sqlservercentral.com/Forums/Topic1400119-1281-1.aspxHow does a message queue relate to a conversation group and the service broker?Is Service Broker used much in the industry? If so, what types of environments is it used for. What is the benefit of asynchronous programming?Is Microsoft Sync Framework used much in the industry?Tue, 25 Dec 2012 19:11:28 GMTGolfer22http://www.sqlservercentral.com/Forums/Topic1374671-1281-1.aspxI’m new to SB. Never had issues with it previously.I have 2 service broker tasks/queues one works fine and I never see it appear when I run select * from sys.conversation_endpointsorder by [lifetime]But a new broker task/queue was set up a few weeks ago. Which always appears in this query. It always has a state of ‘DI’ which would indicate that it is not closing the conversation properly. I have checked and there is only 1 SB contract on in the db. Should there be one for each process.If the conversation closing is the issue how do I check and resolve?ThanksFri, 19 Oct 2012 01:58:19 GMTemile.milnehttp://www.sqlservercentral.com/Forums/Topic1367063-1281-1.aspxHi allBeing new to Service broker I am just trying to understand a couple of points. I am using WMI to automatically create an event notification by creating an alert.One thing I don't understand is when the notification is sent to the queue I have seen it disappear automatically. What is doing this as I thought you had to set an activation of some sort, is this just a built in SQL thing?ThanksTue, 02 Oct 2012 08:06:58 GMTKwisatz78http://www.sqlservercentral.com/Forums/Topic1222293-1281-1.aspxI have a Bookings table as such:-CREATE TABLE [dbo].[Bookings]( [Id] [int] IDENTITY(1,1) NOT NULL, [DateCreated] [smalldatetime] NOT NULL, [Start] [smalldatetime] NOT NULL, [Duration] [smallint] NOT NULL, [Status] [varchar](50) NOT NULL, CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_DateCreated] DEFAULT (getdate()) FOR [DateCreated]GOALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_Status] DEFAULT ('TBA') FOR [Status]GOI have the following data:-insert into bookings (Start, Duration) values ('2011/12/15 10:00', 90)insert into bookings (Start, Duration, Status) values ('2011/12/15 11:00', 120, 'Covered')insert into bookings (Start, Duration, Status) values ('2011/12/16 09:00', 240, 'Cancelled')insert into bookings (Start, Duration) values ('2011/12/17 12:00', 120)insert into bookings (Start, Duration) values ('2011/12/18 15:00', 120)I'm trying to find a way to send alert notifications (either email, sms, push notifications, etc) via my application, if one of my bookings is still TBA and has been on the system for longer than 90 minutes.I would rather stay away from polling the database every minute or so as it degrades performance of my application as i have over a million bookings, so i'm looking for a more elegant solution.I have been trying to make use of Query Notifications to monitor for changes in my Bookings table, which works to a certain extent, but i'm having issues working out how to notify when a TBA booking has been on the system for longer than 90 minutes.So far my query is as below. The 2011/12/15 11:00 is the current time that my app dynamically passes to my query.select Idfrom dbo.Bookingswhere Status = 'TBA'and Start between convert(datetime, '2011/12/15 11:00', 20) and dateadd(d, 7, convert(datetime, '2011/12/15 11:00', 20))and DateCreated < convert(datetime, '2011/12/15 11:00', 20)order by StartThis does initially return TBA bookings for the next 7 days that were created over an hour and a half ago.As bookings get created and amended via my app (bookings can be returned back to TBA), then as the date they get created is never going to be prior to the current date time that i pass, then i'll never receive notifications.Can anyone think of a way to do this?Thu, 15 Dec 2011 04:07:31 GMTdtwilliamshttp://www.sqlservercentral.com/Forums/Topic1257078-1281-1.aspxI would like to call stored procs asynchronously, but there seems to be a delay in service broker so that not all of the procedures finish at around the same time. Actually, the delay is significant at several seconds. I have max readers higher than the amount of procedures I am calling. I have tried using the same conversation, multiple conversations, receiving more than top(1) --- although for high concurrency top(1) should be best --- and using waitfor with a timeout. Here's what it looks like:[code="sql"]create table AsyncExecResults ( token uniqueidentifier primary key, start_time datetime null, finish_time datetime null)gocreate queue AsyncExecQueue;gocreate service AsyncExecService on queue AsyncExecQueue (DEFAULT);GOif object_id('usp_AsyncExecActivated') is not nulldrop procedure usp_AsyncExecActivated;gocreate procedure usp_AsyncExecActivatedasset nocount on;declare @h uniqueidentifier,@messageTypeName sysname,@messageBody varbinary(max),@xmlBody xml,@procedureName sysname,@startTime datetime,@finishTime datetime,@token uniqueidentifier;waitfor(receive top(1)@h = conversation_handle,@messageTypeName = message_type_name,@messageBody = message_bodyfrom AsyncExecQueue), TIMEOUT 5000if (@h is not null)begin if (@messageTypeName = N'DEFAULT') begin select @xmlBody = CAST(@messageBody as xml); select @procedureName = @xmlBody.value('(//procedure/name)[1]', 'sysname'); select @startTime = CURRENT_TIMESTAMP; exec(@procedureName); select @finishTime = CURRENT_TIMESTAMP; select @token = conversation_id from sys.conversation_endpoints where conversation_handle = @h; insert AsyncExecResults (start_time, finish_time, token) values( @startTime, @finishTime, @token); end else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') begin end conversation @h; endendgoalter queue AsyncExecQueue with activation ( procedure_name = usp_AsyncExecActivated , max_queue_readers = 25 , execute as owner , status = on ), status = on, retention = off;goif object_id('usp_AsyncExecInvoke') is not nulldrop procedure usp_AsyncExecInvoke;gocreate procedure usp_AsyncExecInvoke @procedureName sysname, @token uniqueidentifier outputas declare @h uniqueidentifier, @xmlBody xml set nocount on; begin dialog conversation @h from service AsyncExecService to service N'AsyncExecService', 'current database' with encryption = off; select @xmlBody = ( select @procedureName as name for xml path('procedure'), type); send on conversation @h (@xmlBody);goif object_id('usp_MyLongRunningProcedure') is not nulldrop procedure usp_MyLongRunningProcedure;gocreate procedure usp_MyLongRunningProcedureasbegin waitfor delay '00:00:10';endgotruncate table AsyncExecResults;declare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;godeclare @token uniqueidentifier;exec usp_AsyncExecInvoke N'usp_MyLongRunningProcedure', @token output;goselect * from AsyncExecResultsorder by finish_time;[/code]Code borrowed heavily from [url=http://rusanu.com/2009/08/05/asynchronous-procedure-execution/]here[/url] for reference.I have really taken it down to just the bare-bones on purpose here so that I can test performance. What I would like is for there to be as little delay between the first start-time and last finish-time when results are sorted in ascending order. I have done some research and it seems the issue is that service broker is not constantly scanning the queue and then activating whatever is on it. It waits for a bit and then scans again. However, after I run all of the waitfor delay procedures, they should all enter the queue. So by the time SB checks a second time, they should surely all be there for SB to dispatch activation procedures. Thanks for any help!Thu, 23 Feb 2012 20:57:12 GMTblueazulhttp://www.sqlservercentral.com/Forums/Topic1334333-1281-1.aspxHi,I have a problem in SQL2005 so after several days of work without problems, suddenly takes a very slow (sqlserv.exe works much CPU in task manager) and checking the situation I realize that the database is growing and simultaneously a specific queue of messages is not empty anymore but only grows.In the past I had a similar problem but the cause was not explicitly a ROUTE for each queue (note that the RECEIVE command has RETENTION OFF).I think that the causes of the current problem could be two:[b]Hypothesis 1[/b]: Failure to empty the queue causes the increase of database size.[b]Hypothesis 2[/b]: the database is in normal expansion and this causes the slowdown with the consequence of permanent posts in the queue.[b]Hypothesis 1[/b]: I state that I use several queues (33 for different events) and that the problem is always on the same queue (which is the most traffic).Maybe I could work on some of these fronts:a) Increase TIMEOUT (currently 500 for all queues) in the RECEIVE command.b) Increase MAX_QUEUE_READERS (currently 2, the number of cores in PC, for all queues)[b]Hypothesis 2[/b]:I could act in the creation of the database specifying appropriate options SIZE and FILEGROWTH.a) It may make sense to immediately define SIZE as the size that is thought to have the database (eg. in a year)?b) It may make sense to give a SIZE of the log file as half of that applied to the database? Or to avoid misunderstandings reserve the same size for both?Note that my procedures do not use statement BEGIN TRANSACTION / COMMIT TRANSACTION.c) For performance it is better to set the FILEGROWTH in MB or percent? (On internet I read mixed reviews and I'm doing some tests, I think it would make more sense to increase the percentage even though I often read to the contrary).Thanks to those who can give me some helpTue, 24 Jul 2012 03:26:13 GMTgdraghettihttp://www.sqlservercentral.com/Forums/Topic1344640-1281-1.aspxWe have one single conversation in one of our servers that can not be ended. Calling end conversation on it does not return. No messages, no log entries, just sits there. One other thing I've noticed odd is that conversations in this database are no longer cleaned at half an hour after they have been closed: they all simply remain in the closed state.What I've done so far is to see what locks are attempted when I issue an end conversation call:[code="sql"]select * from sys.dm_tran_lockswhere request_session_id = 61 and request_status = 'WAIT'[/code]This outputs:[code="other"]resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK WAIT 1 0 61 0 0 TRANSACTION 321046475 00000000-0000-0000-0000-000000000000 0x00000001DA5ACE20:2:0 0x00000001A8CFF280(1 row(s) affected)[/code]So, end conversation is waiting for a single lock on some meta data that does not get granted. Looking at what process keeps the meta data locked, I find something strange:[code="sql"]select * from sys.dm_tran_lockswhere resource_description = '$hash = 0x2630942b:0xaf7f7df4:0xcc98c2'[/code]output:[code]resource_type resource_subtype resource_database_id resource_description resource_associated_entity_id resource_lock_partition request_mode request_type request_status request_reference_count request_lifetime request_session_id request_exec_context_id request_request_id request_owner_type request_owner_id request_owner_guid request_owner_lockspace_id lock_owner_address------------------------------------------------------------ ------------------------------------------------------------ -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------- ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------- ---------------- ------------------ ----------------------- ------------------ ------------------------------------------------------------ -------------------- ------------------------------------ -------------------------------- ------------------METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK GRANT 1 0 0 0 0 TRANSACTION 307015051 00000000-0000-0000-0000-000000000000 0x00000001DA5ACB10:1:1 0x00000001A8D42E00METADATA CONVERSATION_GROUP 6 $hash = 0x2630942b:0xaf7f7df4:0xcc98c2 0 0 X LOCK WAIT 1 0 61 0 0 TRANSACTION 321046475 00000000-0000-0000-0000-000000000000 0x00000001DA5ACE20:2:0 0x00000001A8CFF280(2 row(s) affected)[/code]The 2nd line is obvious: this is my end conversation attempt waiting for the lock to be granted. It goes away when I kill the end conversation call and comes back when I retry it.But what is keeping that lock at the 1st line? It has a request_session_id of 0, request_request_id of 0 and more columns that make no sense to me. It has been in this state for several days now and it doesn't go away if we stop and start any of our processes. I haven't tried restarting sql server yet, so I don't yet know if that will fix the problem. But I would like to know what is going on before I do that. This is a test system and I don't need something like this to happen in production when we go live.The SQL server machine I'm running this on is Sql server 2005, this is because we're building an extension to an external product for which we can not yet upgrade the server:Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)Please help me find an explanation why this conversation gets blocked and how to make sure it won't happen in production?Tue, 14 Aug 2012 06:11:03 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1335684-1281-1.aspxWe've had a few problems lately with the size of the target queue growing into the multi-millions, and knock-on effects of trying to process all of these messages.The reason for this isn't currently obvious (I have some ideas, but that's a different issue), but once the queue is huge (10-20 million messages waiting) I need to take action to speed it up. The activation sproc runs a variety of other sprocs and some of them are doing lookups on large tables - in some cases, I can clear some of the data to improve speed (and yes, regular pruning of these tables is also on my to-do list!) but the fact that the queue is processing is locking and blocking my attempts to do this.The activation procedure, in common with every example and tutorial I have ever seen, uses an infinite loop with a break when the queue is empty - of course, with 20 million records to process, these procedures will not stop unless there's a poisoned message that stops the queue from within.[code="sql"] BEGIN TRY WHILE (1 = 1) BEGIN BEGIN TRANSACTION; -- Receive all available messages into the table. -- Wait 5 seconds for messages. WAITFOR( RECEIVE [queuing_order] ,[conversation_handle] ,[message_type_name] ,CAST([message_body] AS NVARCHAR(MAX)) FROM [EventDetailsTargetQueue] INTO @ReceiveTable ), TIMEOUT 5000; IF @@ROWCOUNT = 0 BEGIN COMMIT; BREAK; END ELSE...etc.[/code]My thought is to change the loop from [code="sql"]WHILE (1 = 1)[/code]to something like [code="sql"]WHILE (DATEDIFF(minute, @StartDate, GETDATE()) < 2)[/code]to cause the activation procedure to terminate on a regular basis, allowing me to stop the queue gracefully in a reasonable time so that I can carry out whatever remedial action is necessary before restarting it.My question is whether this is a) a good idea and b) will work as I imagine?I'm also curious as to the operation of the RECEIVE... when there are, say, 10 million messages in the queue, what will my activation procedure actually RECEIVE? Not all 10 million presumably - there are 32 threads processing this queue, I reuse each conversation handle for 1000 messages before retiring it.Do I need to add a TOP 10000 or some such to the RECEIVE to allow the loop to go around again and check my 2 minute time limit?Thanks in advance,Dave.Thu, 26 Jul 2012 04:45:55 GMTdave.farmerhttp://www.sqlservercentral.com/Forums/Topic1299623-1281-1.aspxdoes anyone have any best practice details or suggestions on how to choose a value for MAX_QUEUE_READER parameter of a receive queue?i would like to be able to run as many paralell processes as possible, but i'd like to see if anyone has any past experiences or documentation relating to optimal configurations?e.g. 1 per cpu core? 10 per cpu core?the app is importing CSV files into tables and i'm importing multiple files simultaneously to try and acheive maximum IO throughput.any thoughts?Mon, 14 May 2012 09:00:05 GMTMVDBAhttp://www.sqlservercentral.com/Forums/Topic1339437-1281-1.aspxWhat happens if a message is sent, but before acknowledgement is received from the destination, originating server fails over? Therefore the message is still in the transmission_Queue of the sending server and the acknowledgement is queued up in the destination server. When the database comes back online on the mirror server, will it attempt to send the stuff that's in the transmission_Queue? or will the ack be processed before that?The mirror is configured in the route of the destination server.ThanksThu, 02 Aug 2012 12:26:59 GMTSteve-443559http://www.sqlservercentral.com/Forums/Topic1336648-1281-1.aspxHi,on a SQL2005 server where the IP address was changed frequently, suddenly service broker not record more messages. Everything seems to regularly activated and the queues are waiting of receive messages.The cause of the problem may have been the frequent change of IP address of SQL server?ThanksFri, 27 Jul 2012 10:42:11 GMTgdraghettihttp://www.sqlservercentral.com/Forums/Topic1321727-1281-1.aspxI 'm using Service Broker in .net Windows Form application. While I ran the SQL Profiler trace I found many messages taking 1.5 to 30 seconds.It has overall performance implications on application.While in SQL Server error log I found the following error related to service broker.[quote]The query notification dialog on conversation handle '{D001E238-F9B9-E111-9C9E-E83935A8B050}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-7595ba40-1ba1-4878-8ce4-fdb4ac8a384b' because it does not exist.</Description></Error>[/quote]Wed, 27 Jun 2012 01:48:14 GMTmicrosofteehttp://www.sqlservercentral.com/Forums/Topic1305435-1281-1.aspxI have a service broker queue that is serviced by a stored procedure which calls another stored procedure to run an SSIS package.This second procedure runs the SSIS package by creating a Job and running it (see [url=http://code.msdn.microsoft.com/Calling-a-SSIS-Package-a35afefb#]Calling a SSIS Package from a Stored Procedure[/url]).The problem I'm having is that when the queue is serviced I get an error saying that the user does not have execute permissions for the sp_add_job procedure.The user running the process is in the SQLAgentOperator role in msdbWhen running the second stored procedure directly (the one that creates the job) it works perfectly.It's just when that procedure is run from the service broker procedure that it fails.The code to set up the procedure to service the queue is[code="sql"]ALTER QUEUE ProcessUploadQueue WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = Upload, MAX_QUEUE_READERS = 1, EXECUTE AS 'SPUser' )GO[/code]Does anyone have any ideas what I might be doing wrong.ThanksStuartWed, 23 May 2012 16:44:45 GMTStuart Pearsonhttp://www.sqlservercentral.com/Forums/Topic1103927-1281-1.aspxHi AllI've had Service Broker set up between 2 SQL 2005 instances for some months now, and it was working perfectly. However since then, and for no reason I can think of it has stopped working.This is the case through the Dev, Test and Live environments. Unfortunately, it hasn't given me any information to troubleshoot. There are no rows in the sys.transmission_queue table at the originating instance, nor are there any records in the receiving queue.I'm unaware of anything that may have changed when it failed, apart from one element within the transmitted XML including 2s as well as 1s, but this was a recent change and only in dev.I have tried to recreate everything from scratch, but this hasn't helped. As far as I can tell, the messages are being sent and received correctly (SQL Profiler on the destination instance reports inbound messages and Remote Message Acknowledgement, but I cannot see the SP being called that logs the messages into my table.I've been searching the internet for a couple of hours for more troubleshooting tips but I haven't had much luck, is anyone aware of any further steps I could take?Thanks in advance.Thu, 05 May 2011 08:26:45 GMTWoundedParrothttp://www.sqlservercentral.com/Forums/Topic1270887-1281-1.aspxHi Team,I am new to service broker, I need to move the service, Queues from Mydatabase to MSDB system DB due to some issue. Is is passible, I have tried its allow to create and execute, now my doubt is possible to user same service for from service and to service in same server.Pls help me ,,,,GunaThu, 22 Mar 2012 08:21:33 GMThaiguna26http://www.sqlservercentral.com/Forums/Topic1300592-1281-1.aspxWhat we are doing?We are developing a distributed service broker application that processes data on different databases residing on different servers. These service broker applications are deployed are interdependent. These service broker applications process business routines (TSQL statements) on receiving a message from another service broker application. Basically we are going to configure a load balancing configuration as mentioned your book. All the data generated as a result of processing service broker applications are saved locally on each server.What we want to achieve?Synchronize/transfer data to a single Master database. There may be 5-10 huge tables and may not be all the tables in the database.Questions1. Which is a more viable technology SSIS or replication and why?2. Are there any other data transfer alternatives to SSIS or replication for the above mentioned scenario?3. Also since Load balancing configuration is prettymuch a deterministic routing and not a actual load balancing is there a way to actually load balance the work load based on availability of server resources i.e. we are having servers with different configurations some are very high end server and other are mediocre servers. Thanks a lot.~ JagadishTue, 15 May 2012 14:25:59 GMTjagadish1978http://www.sqlservercentral.com/Forums/Topic1299471-1281-1.aspxHi guyslooking for the obvious think that i've overlooked herei'm using service broker to parellelize a process where we import CSV files into a database. i have a procedure that looks at a table with a list of files to process and then starts a conversation and puts a message on the queue for each file.the problem is that even though my proc is sending the messages, nothing is appearing in either queueI've enabled service broker and set trustworthy on - what am i missing ?here is my sample code [code="sql"]if not exists(select top 1 id from dbo.ProcessFile)begin --drop out if there are no files to process returnend--set up the conversationDECLARE @handle uniqueidentifierBEGIN DIALOG CONVERSATION @handleFROM SERVICE FileProcessServiceTO SERVICE 'FileProcessService_receive'ON CONTRACT FileContract;--start sending messagesdeclare @id bigintdeclare @folder varchar(255)declare @filename varchar(255)declare @message xml--loop through the records that are unprocesseddeclare curs1 cursor FAST_FORWARD for select ID,folder,filename from dbo.ProcessFile where status=0open curs1fetch next from curs1 into @id,@folder,@filenamewhile @@FETCH_STATUS=0begin --set the message set @message='<message><file>' --+@folder+'\' --+@filename +'X</file></message>' print convert(varchar(max),@message) --Sends a message ;SEND ON CONVERSATION @handle MESSAGE TYPE ImportFile (@message) --mark it as sent to queue update dbo.ProcessFile set status=1 where ID=@id fetch next from curs1 into @id,@folder,@filenameendclose curs1deallocate curs1--And ends the conversationEND CONVERSATION @handle WITH CLEANUP;[/code]here is the table it reads from (and some sample data)[code="sql"]CREATE TABLE [dbo].[ProcessFile]( [id] [bigint] IDENTITY(1,1) NOT NULL, [folder] [varchar](255) NOT NULL, [filename] [varchar](255) NOT NULL, [status] [int] NOT NULL,PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOinsert into Processfile (folder,filename,status) select 'c:\files','text.csv',0)[/code]Mon, 14 May 2012 05:25:37 GMTMVDBAhttp://www.sqlservercentral.com/Forums/Topic1270832-1281-1.aspxHi Guys, I am new to service broker tech. But many errors are triggering and saying remote service not pickingup.and my Error log is increasing haveily.Description:The query notification dialog on conversation handle '{66D0DCEF-F261-E111-8B0D-0026557FD35C}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-04fbdb53-5e63-4ba2-b048-e5421866cb9f' because it does not exist.</Description></Error>'.what i need to trouble shoot. Is box restarting will give solution for this.becauses we havent restart our box from 2010 onwards. what is permanent fix for this....Thu, 22 Mar 2012 07:25:34 GMTSimha24http://www.sqlservercentral.com/Forums/Topic1265803-1281-1.aspxHi,Before moving the system databases do we have to disable the service broker on MSDB.Kindly clarify my doubt.Thanks,Dev.Tue, 13 Mar 2012 04:57:53 GMTdevanandiiwayhttp://www.sqlservercentral.com/Forums/Topic1266455-1281-1.aspxI am using service broker to make async calls to a stored procedure. All my objects are within a single database.I created a RequestMessage, ReplyMessage, Contract, InitiatorQueue, InitiatorService, TargetQueue and TargetService.I have a stored procedure ProcessTargetQueue attached to a TargetQueue. It calls another stored procedure ExtractData. The ExtractData run remote queries using OPENQUERY and fetch data from remote SQL servers. I created the queue using the below codeCREATE QUEUE TargetQueue WITH STATUS = ON, ACTIVATION(STATUS = ON, PROCEDURE_NAME = <DBName>.dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 25, EXECUTE AS OWNER )I populate the queue using the procedure LoadAndProcessQueue. Typically 10-20 message would be sent during an execution.Whenever a message arrives in TargetQueue the procedure ProcessTargetQueue executed. But it throws an error message :Msg 65535, Level 16, State 1, Line 0SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. The account using which I run the procedure LoadAndProcessQueue is same as the one used for creating all the service broker related objects.When the stored procedures are run individually there is no error. What could be going wrong here? When I ran multiple times the procedure LoadAndProcessQueue I found that during one or two attempts the ProcessTargetQueue executed successfully ! I am using SQL 2008 R2.Any Help Appreciated.Tue, 13 Mar 2012 19:52:15 GMThot chocolatehttp://www.sqlservercentral.com/Forums/Topic1210402-1281-1.aspxHi All, I am looking for references/Information on how to set up SQL Service Broker to send and receive message from databases across different domain. Please provide any references, links where I can get information on this. Thanks, AK.Tue, 22 Nov 2011 11:14:04 GMTanand_vanamhttp://www.sqlservercentral.com/Forums/Topic1229923-1281-1.aspxHi allI have set up a service broker Initiator to Target service on two machines.Both queues send messages fine on there own machines.I then set up binding and routes using a user created from a certificate to send messages from an initiator on one box to target on another.This works fine but now messages sent to the local target just sit in the transmission queue.Looking closer at a trace of the messages the errors are as follows.Audit Broker Conversation 24 8 262496 2 - No Certificate 1 0X01 172.16.32.207 2012-01-04 13:03:50.703 The certificate's private key cannot be found 9924210 0 81e8d523-7925-4ac1-8fc2-f1320688406e Initiator AD 35 1E B7 D0 C9 CE 96 4E CB 30 D8 9C C8 F7 99 0 0 0 Root Agency 0 0 Broker:Message Undeliverable 24 8 262497 1 - Sequenced Message 1 0X01 172.16.32.207 2012-01-04 13:03:50.703 This message could not be delivered because the security context could not be retrieved. 9924210 11229 81e8d523-7925-4ac1-8fc2-f1320688406e 3097A791-A2A2-408C-B9FD-120BC3960731 //Logging/Sync/Msg Initiator //Logging/Sync/Con //Logging/Sync/Out/Ser 16 1 sa Also i noticed on on this site [url]http://msdn.microsoft.com/en-us/library/ms178024.aspx[/url]the folowing statementHowever, if a remote service binding is present where the service_name specified for TO SERVICE matches the name of the local service, Service Broker will use the binding Does anyone know how i can set up a seperate binding or seperate route so that i can send locally to a service the same name as one on a remote box with a binding in place already.I can provide more information if nessecary.Thanks in advance for any help on this. Wed, 04 Jan 2012 07:33:52 GMTashley.wardellhttp://www.sqlservercentral.com/Forums/Topic856617-1281-1.aspxSQL server Service Broker -- Reading same Message by multiple Users in the queueCan a message in a queue can be read by multiple users?I have an Admin Role.Created three users with Admin Role.I m inserting a message into the queue. Is it possible to make the message read by all the three users?Sat, 30 Jan 2010 04:15:07 GMTdeepakknhttp://www.sqlservercentral.com/Forums/Topic1203231-1281-1.aspxHi All,I'm receiving the following error when trying to send a message in service broker. I found the below error message in the sys.transmission_queue on the initiator side."A previously existing connection with the same peer was found after DNS lookup. This connection will be closed. All traffic will be redirected to the previously existing connection. This is an informational message only. No user action is required. State 116."Any kind of suggestion would be highly appreciatedThanks in advance!Wed, 09 Nov 2011 20:03:07 GMTsql server developerhttp://www.sqlservercentral.com/Forums/Topic1192081-1281-1.aspxHi All :-D,[b]Server 1[/b]: Server 2008 SP1, 10.0.2766 (SP1 CU7) - [i]needs updating to , we know.[/i][b]Server 2[/b]: Server 2003 SP2, 9.00.3042.00 (SP2 Vanilla) - [i]needs updating, we know.[/i]We’re seeing problems where service broker messages are not being propagated from the transmission queue on SERVER 1 over to SERVER 2. We are able to kick-start the flow of messages by dropping and creating the Service Broker endpoint on SERVER 1.-- SERVER 1 USE MASTER;DROP ENDPOINT ServiceBrokerEndpointGOCREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION [sa] STATE = STARTED AS TCP ( LISTENER_PORT = 4022 ) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS, ENCRYPTION = DISABLED);GOGRANT CONNECT ON ENDPOINT::ServiceBrokerEndpoint TO PUBLIC;GOFurther to this, It’s evident that a complete server reboot of SERVER 1 did not prevent this issue reoccurring. That is, we still have an issue where Service Broker for SERVER 1.XXXX appears to be no longer able to send out messages (however it is still able to accept incoming messages). We note that Service Broker sometimes wakes up and makes sporadic outbound connections but a point is reached where even this stops happening. We note also that dropping and recreating the (outbound) endpoint is enough to restart connections but not enough to ensure that they continue.This is the sys table I've been watching that shows me when outbound TCP connections cease:-- To prove that the endpoint is working, we should see two or three connections, inbound and outbound (is_accept =0 & =1) select t1.principal_name,t1.last_activity_time,t1.is_accept, -- 1= connections started remotelyt1.receives_posted, -- network receives that have not yet completedt1.is_send_flow_controlled, -- 1= network sends have been postponed due to network flow control because the network is busyt1.is_receive_flow_controlled, -- 1= network receives have been postponed due to flow control because the network is busyt1.total_bytes_sent,t1.total_bytes_received,t1.total_fragments_sent, -- low!t1.total_fragments_received,t1.total_sends, -- low on both connections!t1.total_receives,t2.* from sys.dm_broker_connections t1left outer join sys.dm_exec_connections t2 on t1.connection_id = t2.connection_idAnyone have any thoughts about this one please?Kind Regards,Daniel.Tue, 18 Oct 2011 07:59:58 GMTdaniel.b.handyhttp://www.sqlservercentral.com/Forums/Topic1196027-1281-1.aspxHi,I have a setup where I'm using triggers to capture data changes in a few tables in one database, and send those changes over to another database via service broker. The basic setup works fine, except that occasionally changes that I know had to have happened in a certain order in the source database are arriving in a different order at the target database. For example, let's say I was capturing changes to people, orgs, and the relationships between people and orgs. A user is adding a new person in the source database, then linking that new person to an org. On the target database I'm sometimes getting the message to link the person to an org before I get the message adding the person, even though that's not possible. Any idea how something like this could happen?FWIW, both databases are SQL Server 2008.Tue, 25 Oct 2011 17:16:59 GMTnotoriousdbahttp://www.sqlservercentral.com/Forums/Topic1152385-1281-1.aspxHi All,I'm trying to use Service Broker with a Remote Computer but the Remote computer gets the following error message when they try and connect to my computer "Connection attempt failed with error: '10061(No connection could be made because the target machine actively refused it.)'. "I have done the following checks so far:1. Checked if the TCP/IP is enabled in SQL Config manager2. Make sure that both the ports are not blocked by any firewallsThank youTue, 02 Aug 2011 04:16:23 GMTtt-615680http://www.sqlservercentral.com/Forums/Topic1173429-1281-1.aspxHi,I just had a look at the "Service Broker Statistics" Standard Report (Instance - > right click -> Reports -> Standard Reports -> Service Broker Statistics).The number for "Broker Transaction Rollbacks" is rather high, but the standard test (using [b][url=http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/]ALZDBA's[/url] [/b] great script) didn't show anything unusual (e.g. all queues are empty).The description provided by MS is not really helpful ("The number of rolled-back transactions that contained DML statements related to Service Broker, such as SEND and RECEIVE.")All messages are processed correctly as far as I can see. I'm not seeing a ROLLBACK TRANSACTION in either the SEND or the RECEIVE section.Google didn't return much either.Any hints what this message really means and how to react properly? (if any action is required in the first place...)Mon, 12 Sep 2011 07:44:34 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1111654-1281-1.aspxI am using service broker to execute the SSIS package. Queue is created with Activation ON and created an Activation Stored proc which will invoke the SSIS package. Now if i send more than one message using service broker multiple instance of the SSIS package is getting executed simultaneously. Is there a way to configue the queue is such a way that if one message is under process it will wait till that process get completed and then only it will process the next message.Thanks,PrasanthThu, 19 May 2011 07:02:49 GMTprasanthevhttp://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxHi, I am new to SQL service broker and I don't know anything about. I have to develop an application for my company such that each and every transaction, a entry will be made in audit table for the old value and new value. Our company has many database(though size each one is less). I have to maintain all transaction that is happening in each database in a master database plus each one of the database. Can someone provide me with step by step process that i have to follow to make a robust and flexible application as soon as possible. Any help will be welcome. Thanks in advanceMon, 06 Jun 2011 07:38:20 GMTaavinash9http://www.sqlservercentral.com/Forums/Topic1090026-1281-1.aspxHi I'm using SQL 2005I'm currently using service broker to capture Event Notifications For the event ALTER_LOGIN, I'm having issues capturing the command being generated. I'm only able to capture the event_type and object name.I been playing around with different parameters but unable to make it work. Can anyone confirm if ALTER_LOGIN event generates any TSQL command for data capture?USE [msdb]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[usp_audit_event] ASSET NOCOUNT ONDECLARE @count INTDECLARE @msgtypeid INTDECLARE @msg VARBINARY(MAX)DECLARE @servername varchar(100)DECLARE @eventtype varchar(100)DECLARE @loginname varchar(100)DECLARE @dbname varchar(100)DECLARE @cmd varchar(500)DECLARE @posttime smalldatetimeDECLARE @xmlmsg xmlset @count = (select count(*) from DBANotifyQueue)WHILE @count > 0BEGIN WAITFOR( RECEIVE top(1) @msgtypeid = message_type_id, @msg = message_body FROM DBANotifyQueue ) set @xmlmsg = @msg select (convert(nvarchar(max),@msg)) set @servername = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/ServerName[1]','varchar(100)') ) set @eventtype = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/EventType[1]','varchar(100)') ) set @loginname = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/LoginName[1]','varchar(100)') ) set @dbname =(SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/DatabaseName[1]','varchar(100)')) IF @eventtype = 'DROP_ROLE_MEMBER' set @cmd = 'sp_droprolemember ' + (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/RoleName[1]','varchar(500)')) + ', ' + (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/ObjectName[1]','varchar(500)'))ELSE IF @eventtype = 'ALTER_LOGIN' set @cmd = (SELECT @xmlmsg.value('(/EVENT_INSTANCE/EventType)[1]','varchar(500)')) + ', ' + (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/ObjectName[1]','varchar(500)'))--set @cmd = (SELECT @xmlmsg.value('(/EVENT_INSTANCE/EventType)[1]','varchar(500)')) + ', ' + (SELECT @xmlmsg.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)'))-- set @cmd = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]','varchar(max)')) else set @cmd = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/TSQLCommand[1]/CommandText[1]','varchar(500)')) set @posttime = (SELECT @xmlmsg.value('/EVENT_INSTANCE[1]/PostTime[1]','smalldatetime')) INSERT INTO msdb.dbo.auditevent (servername, eventtype,loginname,dbname,command,posttime) values(@servername,@eventtype,@loginname,@dbname,@cmd,@posttime) set @count = @count - 1ENDThu, 07 Apr 2011 10:00:12 GMTRyan D.http://www.sqlservercentral.com/Forums/Topic566128-1281-1.aspxBelow is the code I am using just to try and get the notification into the queue (After that My plan was to send an email to myself with an alert of some sort.)[code]--Create a queue to receive messages.CREATE QUEUE pmNotifyQueue ;GO--Create a service on the queue that references--the event notifications contract.CREATE SERVICE pmNotifyServiceON QUEUE pmNotifyQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification],);GO--Create the event notification.CREATE EVENT NOTIFICATION pmQueueDisabled ON DATABASE FOR BROKER_QUEUE_DISABLED TO SERVICE 'pmNotifyService', 'current database';[/code]The error I get is :Msg 1098, Level 15, State 1, Line 2The specified event type(s) is/are not valid on the specified target object.This error is from the CREATE EVENT NOTIFICATION step.I can;t figure it out - Any Suggestions?Tue, 09 Sep 2008 06:57:27 GMTJerry Beechhttp://www.sqlservercentral.com/Forums/Topic1086026-1281-1.aspxI'm database developer. For one of the MS SQL project we testing SQL Service Broker replication. Basically we need to replicate table changes from multiple initiators to one target. We implemented environment according to the article:http://www.sqlteam.com/article/centralized-asynchronous-auditing-across-instances-and-servers-with-service-brokerWhen we setup 1 initiator - it works fine, messages are sent and processed . But when we configure second initiator, it sends only the first message, which is preserved in sys.transmission_queue () and all new messages just sit in sys.transmission_queue without processing. No transmission errors is in sys.transmission_queue on 2-nd initiator. It seems that acknowledgment about message delivery and processing on target can't reach 2-nd initiator. 1-st initiator uses service like tcp://192.168.2.130:4022/vpiCore/Audit/DataSender2-nd initiator uses service tcp://192.168.2.116:4022/vpiCore/Audit/DataSenderRoute on target is configured as TRANSPORTCould you help us to resolve the problem, any feedback is highly appreciated, especially GOOD EXAMPLE or linkWed, 30 Mar 2011 02:07:19 GMTvladislav.lashchennikov