I run the code sample below in a SQL Mgt Studio query window. I get no error messages. However, the two SELECT statements at the end produce undesireable results (see comments). My messages to not make it to the target queue; they get stuck in sys.transmission_queue with the error message in the final comment of the code. The server principal "ddddXXXXX" is a Windows Active Directory account that: 1) is a domain admin in the domain that my SQL Server box is a member server of, 2) is the login used by the "SQL Server (MSSQLSERVER)" service account, and 3) is a member of the SQL Server instance's sysadmin fixed server role.
What is it I'm missing that is denying a sysadmin login access to a database?
/***
Service Broker try 1
***/
USE master;
GO
CREATE DATABASE sbRespond;
GO
CREATE DATABASE sbEvent;
GO
USE sbRespond;
GO
CREATE MESSAGE TYPE ActivatedStudyMessage
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT ActivatedStudyContract
(ActivatedStudyMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[ActivatedStudyTargetQueue];
GO
CREATE SERVICE ActivatedStudyTargetService
ON QUEUE [dbo].[ActivatedStudyTargetQueue]
(ActivatedStudyContract);
GO
USE sbEvent;
GO
CREATE MESSAGE TYPE ActivatedStudyMessage
VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT ActivatedStudyContract
(ActivatedStudyMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO
CREATE SERVICE ActivatedStudyInitiatorService
ON QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO
-- Send a message.
USE sbEvent;
GO
BEGIN TRANSACTION;
GO
DECLARE @message XML;
SET @message = N'
<message>
<PROT_ID>123456</PROT_ID>
<StudyID>AAAA1234</StudyID>
</message>
';
DECLARE @conversationHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE ActivatedStudyInitiatorService
TO SERVICE 'ActivatedStudyTargetService'
ON CONTRACT ActivatedStudyContract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE ActivatedStudyMessage
(@message);
END CONVERSATION @conversationHandle;
GO
COMMIT TRANSACTION;
GO
USE sbRespond;
GO
SELECT * FROM [dbo].[ActivatedStudyTargetQueue];
GO
--The query above returns zero records
SELECT * FROM sbEvent.sys.transmission_queue;
GO
--The query above returns two records, each with this "transmission_status" value:
--An exception occurred while enqueueing a message in the target queue.
-- Error: 916, State: 3.
-- The server principal "ddddXXXXX" is not able to access the
-- database "sbRespond" under the current security context.
I'd like to set up a Service Broker queue in one database (dbRespond) on a server so that events in another database (dbEvent) on the same server instance can post messages to the queue. The problem I'm having is that:
The BEGIN DIALOG CONVERSATION needs to reference a Contract that is in the current database, and I want to call BEGIN DIALOG CONVERSATION from dbEvent The target service is in dbRespond. Based on the "Hello World" Service Broker example that comes with SQL 2005, in dbRespond I need to specify the contract in the CREATE SERVICE call that creates the target service. Here, too, the contract must be defined in the current database. How do I deal with needing to have the same one contract in two different databases?
I am experiencing the same problem, and I can't get the easy fix to work. I drop and create the DB's in between tests, so it is not related to having an old certificate in the DB, as in the case of Tilfried.
The situation is as follows:
DB1 owned by login1, has a user for login2; this DB is for the initiator
DB2 owned by login2, has a user for login1; this DB hosts the target
Both DB's have TRUSTWORTHY flag set to ON
Error in sys.transmission_queue: 'Error 916, State 3: The server principal "Login1" is not able to access the database "DB2" under the current security context.
Going on a limp, I decide to add a remote service binding in DB1, binding the user for Login2 to the target service, even though BOL explicitly states that this is only required for cross-server communications. This does change the situation - I still get an error, but a new message is sys.transmission_queue: "Dialog security is unavailable for this covnersation because there is no certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation." I already know that the first option works, but I wanted to get the simple solution running. As for the second option, I doublechecked and the initiating procedure DOES already specify ENCRYPTION = OFF in the BEGIN DIALOG CONVERSATION command. My theory is that the remote service binding somehow forces SB to use encryption, but (a) that is not stated in the error message, and (b) if so, then how to get the messages sent over to the target service without using the binding?
==> EDIT: Just saw that you confirmed this theory in your last reply to Tlifried. So I am indeed back to having to find out how to get this to work without remote service binding - it should be possible, but how???
BTW, SELECT @@VERSION shows that I'm on build 3054, in case it matters.
Between all the errors in BOL and less than helpfull error messages produced by SB, I feel like I'm slowly losing my sanity. Please help!
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.
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
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 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 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.
I have two databases (A and B )on the same SQL Server instance. Both have SSB enabled and running fine within themselves. All athorizations are at present set to dbo.
Recently I had a requirement to start a dialog and send a message from within data base A to a queue via a service that is in database B.
I tried coding the SSB instance in the BEGIN DIALOG then I set up a route and tried that. On both occoasions I got the following on sys.transmission_queue
"An exception occurred while enqueueing a message in the target queue. Error: 916, State: 3. The server principal "sa" is not able to access the database "B" under the current security context."
Is this sometjhing to do with security lock downs in 2005?
I have a VM with SP2013 and SQL Server 2012 SP1. I have installed the powerpivot plugin SP1.But when I run the PowerPivot for SharePoint 2013 configuration tool.
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle); Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>". Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR? If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether? Any help - greatly appreciated.
I have configured an instance to use static port, say 1435, and I turned off the Browser service.
I would like to know if I can use SSMSE to connect to the instance database directly. The instance is listening at port 1435 and accepting connection requests from other methods.
What I need to put into the "Server name: " field?
Normally, it would be like: <serverIP><InstanceID> with Browser turned on.
Now how do I add the port information to it, I tried
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;
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream". OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
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
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
We have a customer whos database just grows and grows. Not the customers own tables, but the:
sys.sysconvgroup sys.sysdesend sys.sysdercv
And these tables are linked to the Service Broker, and according to http://msdn2.microsoft.com/en-us/library/ms179503.aspx these tables exists in every database and are used by the Service Broker.
Now to my questions =)
HOW do I delete rows from these tables? How come these tables hust grows and grows, could it be any setting in the SQL 2005 Server or is it the customer who has programmed his application wrong?
I'm in the process of doing the initial research for the architecture of a large scale, transactional messages routing platform.
My initial design called for a series of MSMQ queues and Windows Services, written in C#, to process the messages in these queues. There will be incoming and outgoing queues, queues to store unroutable messages, etc.
My application will be routing many hundreds of thousands (and eventually millions) of messages per day. These message are very small (< 200 bytes each) and must be routed very quickly. (<1 second processing overhead per message for high priority messages.)
Using the term "routing" may be a bit misleading. The messages arrive via TCP socket connections. I will just need to take in a message, examine its intended destination, and send it to one of several outgoing socket connections, likely on different machines. Some messages require higher priority routing than others, but I don't need multi-hop routing or anything like that.
Of great concern to me is that there are absolutely no single points of failure in the system. Because of this I was considering using a combination of MSMQ and Windows Services in a Clustered environment.
Can the Service Broker provide me with this kind of functionality? If so, how well does it perform and scale? Is it a better choice for messaging applications that require high transactional throughput than MSMQ?
I'm just trying to get an idea of what products/services I should look into further.
i'm new with service broker and need to develop mail site to send mass email and decide to use sevice broker i'm make aqueue ,sevice and all function for run service borker and creat databasie mail profile
then test it but it's don't work please help me to fine what's the problem ?
thanks very mcuh for every one read my request and response to me thanks very mcuh
I am new to service broker and would like a little help please. I have a SP which gathers information from a collection of tables. Depending on the data gathered it may or may not begin a dialog conversation with a service broker queue. What i'm needing to know is should at the end of the SP once the required message has been sent should i end the conversation or not?
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?