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.
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]'
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.
Hi All, I want to send notification to SSRS on change in database for that I am using SSB to send message to SQL Server Reporting Services. Can I use SSB and if yes, how? Please guide me I am new to this.
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.
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.
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?
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.
when ever I send my message thru Service Broker I am getting an error message like this "
"Dialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement."
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
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
While attempting to configure DTC on a Windows xp Pro, SP 2 machine I receive the error message [DTC was installed by the SQL Server. Please reinstall!] Where do I find this installation file to complete the requested task -- any help would be awesome -- I have tried down loading hotfixes, and security updates that mention fixing problems with DTC -- but alas they did not fix my error ;-)
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 downloaded this program, and when trying to intall I am receiving an error meesage that there is insufficient disk space to install.
I have 98GB available on C and 107 GB available on D. I am on an IBM Intellistation dual processor 3,08 Ghz with 4GB RAM and 240 GB Disk (those are "G"'s), so I have tons of capacity. This has to be a problem with the .msi - any suggestions?
I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.
I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.
Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.
Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?
Has anyone seen this in profiler? I have two brokers on different servers with one of them being the initiator. All messages end up sitting in the initiator's transmission queue. Profiler on the target broker's machine displays this for every attempt to send by the initiator:
A corrupt message has been received. The End of Conversation and Error flags may not be set in the first sequenced message. This occorred in the message with Conversation ID '...', Initiator: 1, and Message sequence number: 0
In case it's relevant encryption is disabled and endpoints on both servers use windows authentication.
I installed Micrsoft Server Desktop Engine from the CD I received in my ASP.net book. The installation went fine and then I went to the DOS prompt and did the "setup SAPWD..." and it ran. I restarted my computer and opened ASP Matrix and went to the Data tab and proceed to connect to the server. It gave me an error message it couldn't connect.
So I uninstalled MSDE and made sure the sql2ksp3 folder was gone. So I went ahead and installed the MSDE again from the CD again and then went to DOS to do the setup. It ran through the setup until the very end with 11 seconds remaining and received error message " Setup failed to configure the server. Refer to the server error logs and setup logs for more information. I have tried it several times and it always stops around 11 seconds. I have tried to install the download from microsoft.com instead the CD and that didn't work also. So at this point I am stuck. PLEASE HELP!
I also checked my Registry keys for a file called PendingFileRenameOperations and it wasn't in the registry keys.
If anyone has any suggestions I would appreciate it.
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.
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.
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.
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.
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.
It will be great to have an update on MS plans to solve the problem of using Service Broker for remote users who sit behind the NAT. Any news will be appreciated.
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...
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.
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?
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...
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;