Service Broker &&"TO Service Could Not Be Found&&" Message Origin: &&"Transport&&"
I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....
CREATE PROCEDURE [usp_ProcessMessage]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body AS VARBINARY(MAX)
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR(RECEIVE TOP (1)
@conversation_handle = conversation_handle,
@message_body = message_body
FROM [tcp://mydomain.com/INITIATE/MyQueue]
), TIMEOUT 1000;
IF (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
END CONVERSATION @conversation_handle
IF @message_body IS NOT NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversation_handle
FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]
WITH ENCRYPTION = ON, LIFETIME = 600;
SEND ON CONVERSATION @conversation_handle
MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]
(@message_body);
END
COMMIT;
END
END
GO
My endpoints are created like so:
CREATE ENDPOINT MyEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)
GO
GRANT CONNECT TO CertOwner
GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner
GO
And my routes like so:
GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner
GO
CREATE REMOTE SERVICE BINDING [MyCertificateBinding]
TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'
WITH USER = CertOwner,
ANONYMOUS=OFF
CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]
WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',
BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
ADDRESS = N'TCP://xxx.xx.xx.xx:4022'
GO
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
SQL Service Broker - Transport Security
If all my SQL Server instances are mutually trusted, am planning to implement transport layer security with Windows authentication. My query is that if I'm using Windows authentication do I need certificates to be created? Though I am using Transport security, I can achieve encryption by ENCRYPTION - ON in the Begin Dialogue conversation. I assume Message integrity using MD5 signatures are by default provided by Service broker irrespective of whichever service options we choose? Can I have some article references on how these security mechanisms will impact the performace of Service broker communications? Thanks a lot,
View Replies !
View Related
Service Broker Dialog And Transport Security
what is the difference between the Dialog security and the transport security? If I disable the dialog security can the messages be sent to the a different SQL server instance. As the transport security will encrypt the messages. If I don't create a certificate to be used by the transport security can the messages be sent to another SQL server instance? Can a message be sent to another SQL Server instance without creating a REMOTE Service Binding?
View Replies !
View Related
Service Broker: Trigger On New Message
Hi, I have a small problem with my two databases ( A and B ). On database A I have a queue set up for receiving messages from a service broker which are sent via a stored procedure from database B ... Each time a message hits the queue on database A I would like to run a stored procedure that takes the message and actions it. I have my stored procedures in place but can't figure out how to trigger a procedure each time a message is received. I have read this ( http://technet.microsoft.com/en-us/library/ms171601.aspx ) but would really appreciate someone posting an example of setting up queue activation. Many thanks Chris
View Replies !
View Related
Service Broker Message Dispatcher Error
One of my customers' SQL Server 2005 databases, which was set up to receive messages from another SQL Server 2005 database on another server has been re-installed by the customer and now the message passing no longer works. After recreating the End Point and re-enabling the Service Broker on the target database, I am now getting messages in the SQL Server error log. An error occurred in the service broker message dispatcher, Error: 15581 State: 3. and Error: 9644, Severity 16, State: 14. The two errors repeat alternately ad nauseum. Running a trace on the target database server, for the Broker:Message Undeliverable, gives an endless stream of these entries: This message could not be delivered because an internal error (code 15581, state 3) was encountered while processing it. Check the error log for more information. Any ideas on how to fix this system?
View Replies !
View Related
Message Could Not Be Delivered Errors In Service Broker
Hi, I am using service broker in between two database servers. following is the way i am sending and receiving messages Send BEGIN TRAN BEGIN DIALOG CONVERSATION @handle FROM SERVICE @SendService TO SERVICE @ReceiveService ON CONTRACT @Contract WITH LIFETIME = @lifetime; SEND ON CONVERSATION @handle MESSAGE TYPE @xmlMessageType(@xmlMessage); COMMIT Receive BEGIN TRAN; RECEIVE TOP(1) @xmlMessage = message_body, @handle = conversation_handle, @message_type = message_type_name FROM TransactionQueue; ---------------------------------------------------------------------------------------------------- -- Check to make sure a message was returned to process. In theory this should never happen. ---------------------------------------------------------------------------------------------------- IF @@rowcount = 1 BEGIN IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN END CONVERSATION @handle; COMMIT RETURN 0 END IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN RAISERROR(N'Received error %s from service [Target]', 10, 1) END CONVERSATION @handle; COMMIT RETURN 0 END SET @sql = 'EXEC '+@message_processor_name+' @xml' BEGIN TRAN EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage COMMIT TRAN END CONVERSATION @handle; END COMMIT I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler. 1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation. 2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message. Let me know if you need any other information
View Replies !
View Related
Service Broker Sending Message To Self. Start Conversation With Self
I've got a situation where I want to put request message on a queue. Because starting a conversation is the only way to put messages on a queue I have to start a conversation with myself. So my Begin Dialog Statement looks something like this: DECLARE @conversation_handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @conversation_handle FROM SERVICE [ServiceName1] TO SERVICE 'ServiceName1' ON CONTRACT [ContractName1] WITH ENCRYPTION = OFF; SELECT @conversation_handle AS ConversationHandle I haven't noticed any problems with doing this but I wanted to know if there was anything wrong with it. Does someone know what problems this might cause?
View Replies !
View Related
Service Broker Message On Remote Instance Not Working
I was trying to send a message on a different instance in the network using service broker. I have created the endpoint and route both the side. I could see that the messages are in the transmission_queue in the sender side with no error in the status. What could be the reason. I am doing the following: CREATE ROUTE inst02 WITH SERVICE_NAME = 'SERVICE2', ADDRESS = 'tcp://10.14.43.149:2341' in the send script i am using this: BEGIN DIALOG CONVERSATION @dialog_handle FROM SERVICE [SERVICE1] TO SERVICE 'SERVICE2' ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ; in the sending side if i change the endpoint's authentication as windows kerberos , then i get thefollowing error in the transmission queue: "Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 66." I also have given access to the remote user on this endpoint(on the recever side) using this : GRANT CONNECT ON ENDPOINT::Endpoint_test to paras I am executing the sending side send script using the same user wich has access to the remote endpoint. Can some one resolve this issue. Thanks
View Replies !
View Related
The SQL Server Service Broker For The Current Database Is Not Enabled, And As A Result Query Notifications Are Not Supported. Please Enable The Service Broker For This Database If You Wish To Use Notifications.
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View Replies !
View Related
App Receiving &&"Options&&" Message From Service Broker
I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx ) When I run this app against a remote SQL Server, I receive the message "Updated" which I expect. But when I run the same app against the local machine SQL Server, I receive the message "Options". Does anyone know if there are SQL Server options that must be set to certain values? I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.
View Replies !
View Related
Service Broker And .net Windows Service
I am doing some research to see if the Service Broker technology would help my company with our Enterprise application. Here is our scenario: We have a 3 tier system. The first tier needs to contact the second tier asynchronously. Hence, using queues is a good option. However, the process that needs to happen on the second tier is mostly process intensive with little database updates. Is it still worth our time to use Service Broker? I like the concept of Activation that Service Broker provides. But, from what I am reading most of the documentation describes activation as a way to call another stored proc. I definitely dont' want to do any process intensive work on the SQL server. So here comes my question... How would I use a windows service to listen to the activation event from the Service Broker. I could have multiple windows services watching the same queue (scalable). Would I have to handle collisions myself? If so, I think I would rather keep it simple, and just use a simple table as my queue. Thanks for your comments in advance... Vijay.
View Replies !
View Related
Architectural (broker) Place Of SQL Service Broker
Hi, I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight. I look forward to some responses. Kind regards, Neeva
View Replies !
View Related
SQL Service Broker
Hi to all, I want to study Sql server Service broker, have some questions1. What is the use of service broker ?2. Where this will use ? (With example)3. How to enable Service broker? Because i have sql server 2005 version but no folder like service broker.
View Replies !
View Related
SQL Service Broker
Hello, I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how they "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files? Mike
View Replies !
View Related
Service Broker
Is SQL Service Broker part of SQL Server Express or is there an add-in that I need?Currently I am not seeing the Service Broker Directory Folder under the database that I created.ThanksDoug
View Replies !
View Related
Service Broker Example.
Im having a hard time understanding everything required to create a simple Service Broker example. Can someone please assist? Source code would be ideal, but if not "do this, do that" would even be helpful. Thanks.
View Replies !
View Related
Service Broker
I have tried the following, each runs successfully with no error, but nothing is in the queues, what can be the issue? CREATE MESSAGE TYPE SentMsgType VALIDATION = WELL_FORMED_XML; CREATE CONTRACT MQContract (SentMsgType SENT BY ANY ); CREATE QUEUE SentQueue WITH STATUS=ON, RETENTION=OFF; CREATE QUEUE ReceivedQueue WITH STATUS=ON, RETENTION=OFF; CREATE SERVICE SentService ON QUEUE SentQueue (MQContract); CREATE SERVICE ReceivedService ON QUEUE ReceivedQueue (MQContract); SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[insertTrigger] ON [dbo].[tblBBB] FOR INSERT AS BEGIN SET NOCOUNT ON; DECLARE @handle uniqueidentifier DECLARE @msgBody nvarchar(500) select @msgBody = someString from inserted BEGIN DIALOG CONVERSATION @handle FROM SERVICE SentService TO SERVICE 'ReceivedService', 'CURRENT DATABASE' ON CONTRACT MQContract; --Sends a message SEND ON CONVERSATION @handle MESSAGE TYPE SentMsgType ('<message>' + @msgBody + '</message>') END CONVERSATION @handle WITH CLEANUP; END SELECT * FROM SentQueue SELECT * FROM ReceivedQueue;
View Replies !
View Related
Service Broker + .Net 1.1
Hi, Is it possible to develop Service Broker in .Net 1.1 (VS 2003)? Currently I have a project developed in .Net 1.1 and I want to add a new method utilize the message queue concept (instead of using MSMQ, using Service Broker SQL 2005), although my DB is SQL server 2005. Thanks,
View Replies !
View Related
Is SQl Service Broker What I Need??
Hi, I am looking at the Service Broker as a way to notify multiple clients that there has been data changed on a table in the shared database. These clients may or may not be online. When there is a change, the notification should fire off a query to refresh the clients local cache. Is this a situation where Service Broker would help me? Can multiple clients recieve the notification at different times ( some recieve while online, some recieve when they come back online)? Any help on this would be appreciated. It seems from what I read that the messages are pulled off the queue when a notification has taken place. Is this correct? If so, can I set it to behave differently? Thanks, -paul
View Replies !
View Related
Service Broker
My service broker seems to be broken... The database was restored from another crashed server but i have tried the ALTER AUTHORIZATION ON DATABASE::[SPYDERONTHEWEB] TO [SA]; The error i'm getting is Service Broker needs to access the master key in the database 'SpyderOnTheWeb'. Error code 25. The master key has to exist and th service master key encryption is required. Error: 28054, Severity 11, State: 1.
View Replies !
View Related
Service Broker
Hello , I am trying to Implement distribution of the Stock Quotes over the LAN(only within the Network) and showing the live changing stock Quotes on the front end (in datagrid) installed at each clients desktop.I am receiving the Stock prices over the TCP / IP from the Stock Exchange. I am recieving atleast 10-15 messages per second over the TCP / IP from the Stock Exchange. Now i need to distribute this feed to Each connected client. I tried doing it from TCP / IP , but in vein. Can we install the SQL 2005 Database Client Version on every client and use Service broker instaed of Live TCP / IP connections programmatically? Ideally Can i dump the meesages from Stock Exchange in to each connected client's database locally and each front end application will keep a record of all the incomming messages.i.e Front end have a notification event , it will referesh the Datagrid in Front end accordingly... ALL my front end application are made in dot net Pls suggest if this above workflow will help me Yugant
View Replies !
View Related
Service Broker
I am trying to implement service broker. I send a message from my application code to the database to execute a specific stored procedure. How do i return the result set obtained by the execution of the stored procedure to the application.
View Replies !
View Related
Service Broker From Behind The NAT
Let's assume the situation: we have Initiator and Target. Target is behind ISP's NAT and can't be published outside. So, when Initiator sends a message to Target, Target will not be able to establish a backward connection and will not send an acknowledge. Initiator will retry and retry...
View Replies !
View Related
Sql Dependency And Service Broker.
Hi everyone, Can anyone let meknow how do i enable a service broker. I am trying to enable a service broker for an issuetracker application to get change of events in my database. When ever i try enabling it using the ALTER DATABASE [ databse] set Enable_Broker. it takes abt more that 2 hrs or more but doesnt show as enabled. Thanks in Advance, Pawan Venugopal
View Replies !
View Related
Communicate With Service Broker In C#?
So SQLDependencies failed to do what I wanted them to do for my Cache Invalidating, so i'm going to humor another possibility for a half day - Triggers on my database table that communicate messages to my C# inside my ASP.NET App. Any advice on how to tap into a message queue with C#? I'm thinking that my messages could be 1 of about 100 different strings as far as what occurred on the Database Tables
View Replies !
View Related
2005: Service Broker?
Hello,I am learning SQL Server and I would like to ask you a question: whatare applications of Service Broker? I would like to know in whatpuproses it can be used in database systems.Thank you very much for you answers/RAM/
View Replies !
View Related
SSIS And Service Broker
I am trying to use SSI Sto receive messages from a Service Broker Queue and continue to have difficulty doing so. I have come to the point where I need to get the messages that share a conversation with this BatchEnd message type before I get any other messages, as this message type tells me information about how many others there are. I built this script and ran it in management studio with success and felt it should work for me running as the sql for an OLE DB source. declare @ch uniqueidentifier; select @ch = conversation_handle from dm.[consultant queue] where message_type_name = 'BatchEnd'; Receive conversation_handle, message_type_name, message_body from dm.[consultant queue] where conversation_handle = @ch; But SSIS constantly complains about it. I finally profiled SSIS to catch the sql that it is executing. It is below and this fails with an error about a missing conversation_handle. declare @p1 int exec sp_prepare @p1 output,NULL,N'declare @ch uniqueidentifier; select @ch = conversation_handle from dm.[consultant queue] where message_type_name = ''BatchEnd''; Receive conversation_handle, message_type_name, message_body from dm.[consultant queue] where conversation_handle = @ch;',1 select @p1 go Now I'm not sure which team may own this, but shouldn't this sp_prepare work. If I change this to use a table variable then the prepare seems to work just fine, but then I never actually get any data back into SSIS. It is almost like it calls it twice; although, I could not confirm that with profiler. I'll cross post this in the Service Broker forum too. Thanks!
View Replies !
View Related
Service Broker And SSIS Together ?
Hello, I want to use Service Broker and SSIS in a integration project where the queue contains Xml data and the SSIS package processes and sends it to a destination. What is a good way to do this? Is it possible for the queue to execute the package with the data as a parameter, or what is good practise when combining this techniques? I am not familiar with Service Broker so i don´t know what you can do with it when data are loaded in it. Would be grateful for some hints to put me in a good direction.. /Erik B
View Replies !
View Related
Using Service Broker To Synchronize Non SQL DB
Hi everyone, I would like to know if using Service Broker to synchronize another non MS DB with this technologie is suitable? My idea is to place in messages the modification of any table and then have a windows service or web service to read this Service Broker Messages, and then update another database (Intersystems Caché). Any other ideas are welcome! Best regards, Sam
View Replies !
View Related
Help Implementing Service Broker
I am working on a project where I want to implement service broker. A lot of the examples that I have seen are all based on T-SQL. Here are my questions. 1. I have a web page that allows a user to type in an order. How do I put a message on the queue using a .NET assembly? Is there some sort of API set? Do I just pass the message to a stored procedure that puts the message on a queue? 2. Are there any samples that illustrate how to read messages off a queue using a console application or windows service? Is it possible to read the messages off of the queue with a console application or do you have to subscribe to the queue activation event and write a service program that runs under the control of service broker? All of the data that I need to fill the order has to come from a DB2 database on the mainframe. Therefore, I am stuck with creating an external application that processes the messages. 3. In order to use Service Broker, do I have to install Notification Services as well?
View Replies !
View Related
Looking For Info On Using Service Broker With .net
Hi!! I'm new to service brooker and I'm looking for a tutorial or an example about a .net application sending messages to a queue in Service Brooker, and other .net application consuming the messages. Can you giveme some links or somethig about this? Until now I have found only info about using this on SQL Server, but not interacting with an external application made in dot.net. Thanks in advance Andres Garcia
View Replies !
View Related
Service Broker && 2 DBs, Using Set Trustworthy Off.
I'm trying to create an audit database containing a log table, which will be filled with some info, according to the case. These info will be send from the db being audited and then saved in the audit db using service broker. Both databases are on the same instance. Is it possible to have both databases set with trustworthy off? How to do that? I've tried this: 1 - Create a certificate; 2 - Create a login from the certificate; 3 - Create an user in each database from that login and 4 - Alter the queue from db being audited, including the "execute as" But I get this error: Cannot execute as the database principal because the principal "usrServiceBroker" does not exist, this type of principal cannot be impersonated, or you do not have permission. 5 - Impersonate these users, with no success. 6 - use execute as 'dbo' in the procedures related to the queues. Any clues?
View Replies !
View Related
How To Run The Service Broker Samples
I am trying to run the Readme_HelloWorld sample on the SQL server 2005 Books online. But don't know where I can get the setup.sql, SendMessage.sql, ReceiveMessage.sql scripts. I followed two examples on the blog, but they did not work at all. Here are the scripts: /* example 1 */ create database TestSB go use TestSB go create queue TxQ create queue RxQ create message type Msg create contract MsgContract(Msg SENT BY ANY) create service TxSve on Queue TxQ Create service RxSve on Queue RxQ declare @h uniqueidentifier begin dialog conversation @h from service TxSve to Service 'RxSve' on contract MsgContract; send on conversation @h Message type Msg ('<hello>World</hello>') end conversation @h with CLEANUP; select * from RxQ /* example 2 */ use testsb go create message type HelloMessage validation = NONE go /** 2. create contract **/ create contract HelloContract (HelloMessage SENT by initiator) /** 3. create queue **/ create queue SenderQueue create queue ReceiverQueue /** 4. create service **/ create service sender on queue SenderQueue create service receiver on queue ReceiverQueue(HelloContract) declare @handle uniqueidentifier declare @message nvarchar(100) begin begin transaction; begin dialog conversation @handle from service sender to service 'receiver' on contract HelloContract set @message = N'Hello, World'; send on conversation @handle message type HelloMessage(@message) end conversation @handle commit transaction end go Receive convert(nvarchar(max), message_body) as message1 from ReceiverQueue select convert(nvarchar(max), message_body) as message1 from ReceiverQueue select * from SenderQueue select * from Receiverqueue Any assistance or WORKING samples are greatly appreciated.
View Replies !
View Related
Disable Service Broker
Using SQL 2005 SP2 I enabled service broker, added 2 new roles (sender and receiver) with rights below: --for sender GRANT CREATE PROCEDURE to [sender] GRANT CREATE QUEUE to [sender] GRANT CREATE SERVICE to [sender] GRANT REFERENCES on CONTRACT::[http://schemas.Microsoft.com/SQL/Notifications/PostQueryNotification] to [sender] GRANT VIEW DEFINITION TO [sender] -- for receiver GRANT SELECT to [receiver] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [receiver] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [receiver] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [receiver] I added a new Schema, a new SQL user as well and altered that SQL user WITH DEFAULT_SCHEMA so I can use it for my purpose and the process is all working well however... The problem I have is to rollback and disable(drop) all references because after I remove: - all the members from the role - drop the two roles - drop all procedures, services (DROP SERVICE ...) and queues (DROP QUEUE....) owned by the schema I still can't drop the schema because sys.objects still has entries where type = 'SQ' but that they were supposedly removed by the commands I executed before Did anyone else has seen this before?
View Replies !
View Related
Is Service Broker Solution For This ?
Hi guys, I have an application for which I need to send emails based on results of query. list of subscribers will change dynamically. I only need notify via emails . Is notification services solution for this ? Will pure service broker based solution without notification service work ? thinking of using atleast service broker since I want to enforce guranteed delivery. what do you think ? Please let me know venkata
View Replies !
View Related
Service Broker Locking
Hello, we are experiencing locking issues with our Service Broker. Every once in a while, something happens in the process to where X and IX locks will get stuck on the target queue. These locks seem to never get released, and therefore nothing can access the target queue after that. We have no idea why this is happening. I am wondering, is this a symptom of Fire and Forget? We are running SQL 2005 SP2. Here is the output from sp_lock: 53 21 0 0 DB S GRANT 53 21 0 0 MD 20(10003:0:0) Sch-S GRANT 53 21 1013578649 2 KEY (88036b58cdc5) X GRANT 53 21 1013578649 1 KEY (860380c6addb) X GRANT 53 21 997578592 0 TAB IX GRANT 53 21 1013578649 0 TAB IX GRANT 53 21 0 0 MD 25(b7004c49:4d37e776:12f048) X GRANT 53 21 1013578649 2 KEY (89030960e5ef) X GRANT 53 21 1013578649 1 KEY (85031ec60717) X GRANT objectid 1013578649 = queue_messages_997578592 (internal table) objectid 997578592 = ds_ccs_notification_target_queue spid 53 is running the following stored proc: CREATE PROCEDURE [dbo].[ds_ccs_notification_ret] @timeout int, @fetch_size int AS BEGIN DECLARE @cgid UNIQUEIDENTIFIER; WAITFOR(GET CONVERSATION GROUP @cgid FROM [ds_ccs_notification_target_queue] ), TIMEOUT @timeout; IF (@cgid is not NULL) BEGIN RECEIVE top(@fetch_size) message_type_name, conversation_handle, CAST(message_body AS XML) AS message_body, message_type_id FROM [ds_ccs_notification_target_queue] WHERE conversation_group_id = @cgid; END; END; Any ideas? Thanks, Mitch
View Replies !
View Related
Service Broker Errors
Hello everyone, I implemented query notification using SqlDependency(). Normally, it works well. But once or twice a day, I noticed following messages in the event viewer. When that happens, my application does not a notification when there is changes in the database table: Service Broker needs to access the master key in the database 'MyDb'. Error code:25. The master key has to exist and the service master key encryption is required. The query notification dialog on conversation handle '{9FAEDFF0-4A61-DC11-9C99-000C2955F097}.' 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-a6d061ab-d89d-4735-91ee-8fdc7f9621f7' because it does not exist.</Description></Error>'. The query notification dialog on conversation handle '{62316FAB-4A61-DC11-9C99-000C2955F097}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'. I have a logic in my code that after 30 minutes of silence, it re-establish the connection by re-instantiating SqlDependency and it works okay from that point on. From that observation, it seems that query notification works in general but SQL server occasionaly throws an exception, it seems. Also my code did not get SqlNotificationInfo.Error when that error occurs. I appreciate any help. Thanks. Moon
View Replies !
View Related
Web Services And Service Broker
We are in the process of rewiting an application that has client machines connected to a central server using A VPN to get configuration changes. As the number of machines grows we don't believe that this will continue to perform properly. Weare expecting hundres if not thousands of machines to be connected. We developed a Windows service for the cleint machine that communicates with a Web Service. It is called asyncronously and that works well. The problem lies with the Web Service. We want to use Service Broker for messaging with messages sent from the Database when any one of 5 tables have changes written. If I use a WaitFor call to service broker, that causes a connection to be open at all times. SQLDependency would do the same thing. What we are looking for is a way for a activated Stored Procedure to wake up a thread or notify the Web service that a message is waiting. Mind you all of the client machines will have their own queue for messaging. What this makes me think is that I have to have a way for the SP to know which connection on the Web Service needs to know that a message is waiting. Is this possible? or do I need to have the Web Service keep looking or use WaitFor? I guess I am looking at it as having the activated SP raise an event based on which queue received the message. Is Dynamic Event names? Meaning can I have the Web service wait for an event say MessageReady_{MachineName} ie: messageReady_74 or messageReady_81 etc... and have the activated SP raise the proper event dynamically. If I must use WaitFor (indefinately) is thee a problem with this with say 1000 connectinos? Is it possible for SQLDependency to watch a Queue? That way the Web Service handles the dynamic naming based on the machine that connected.
View Replies !
View Related
Service Broker From SSIS
Hello, I want to use Service Broker to drop some SQL from a loop in SSIS task into a Service Broker queue. Can anyone point me in the right direction, examples, samples? I have an ExecuteSQL task which returns a set of tablenames, which will be iterated to, and I want to send an EXEC mySP 'myTable' to Service Broker, to asynchronously execute. I have created a queue and a service, but I'm not sure what the next step is to join the two together. any help, or links will be gratefully received! Many thanks
View Replies !
View Related
Monitoring Service Broker
Hi! We have next problem in production. We have one target that accepts messages from many initiators. Some time target can not get message from some initiator. It may be due hardware problem, for example technitian switch off router. In general service broker succeed to receive all messages when hardware problem was solved (if it takes less than retantion time on initiator side). I would like to build some job that would look at queue of target and could say (or trigger) that from the initiator A for some period of time target did not get any message or did not succeed to close conversation or it constantly have some connectivity problem (like duplicated message). Any idea?
View Replies !
View Related
Disabled Service Broker
I have a windows service that uses SqlDependency. It has been working fine until today where it has died. I have traced the problem back to the service broker being disabled, however I have not disabled the service broker. Is there anyway that the service broker can become disabled ?
View Replies !
View Related
Replacing FTP With Service Broker
Hi there Could you use service broker to replace FTP? We currently move hundreds of files around from source systems to destination systems. Some systems share the same files, some only recieve a subset of the data in the files. This typically takes place early morning the next day. Would it be possible, and I guess more importantly, has anyone done this; Use service broker on all the source systems,sending to a central routing server that determines which destination servers require which piece of data and then forward it onto those servers. It would need to handle very high volumes of data. The central server would also be required to transform some of the data as it passes through. This would completely change the way in which we would exchange data around our network, allowing us to process more realtime than 1 day late. Is there anyone who has done this, are their any case studies on this? Thank you in advance. Mark S
View Replies !
View Related
Service Broker Permissions
Hi, in the development env. I created the 2 dbs used by service brokers, the service brokers objects (messages, queues etc...). The schema applied to tables and Activation SP is [dbo] and also the queues are executed as [dbo]. Everything works fine!Cool! Now that we have to deploy evertything on production I would like that the service broker conversation runs using a specif user ( in this way when I log the Service Broker's errot in the event log I see this specific user and not my name) Which kind of permission I have to give to this the user .. it is enought that I assign to it the schema DBO or I have to change the definition of my queues( execute as '[dbo]') or to create a new schema? Thankx everybody fo any help! Marina B.
View Replies !
View Related
Service Broker Errors
Our production database was running fine and all of sudden the service broker started throwing errors "Service Broker needs to access the master key in the database 'Intellicache'. Error code:25. The master key has to exist and the service master key encryption is required. Error: 28054, Severity: 11, State: 1. " We dont have a master key. I tried to re enable the service broker thing again but it did fix either. Do some one else too have a similar issue? Can some one help on this please?
View Replies !
View Related
|
TRACKER
