Hi, I have read that service broker can be used to manage and schedule jobs. but how is this more of an advantage compared to the current sql server job scheduling using the sql server agent. thanks in advance. Would appreciate if somebody can answer immediately
View 2 RepliesHi,
I'm looking to run a "job" on a SQL database regularly (like scheduled by a "task scheduler") to updated my database.
It appears I can use SQL Service broker to run such a job.
Can I embed a "task scheduler" inside my SQL Server database to trigger this job each X seconds?
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
View 1 Replies View RelatedHi,
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
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
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.
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 2 Replies View RelatedIm 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.
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 4 Replies View RelatedMy 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.
Hi
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.
Leonid.
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
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,
Hi all
if any one have any white paper or artical cover this issue kindly i need it
thanks , regards
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
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 8 Replies View RelatedI 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;
How to create service broker and whic version is supported to create serveice broker.
can you plz exlain to create servece broker from the scratch
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
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 3 Replies View RelatedHi,
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?
Please respond as soon as possible.
Best regards
.Henrik
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
Hello people
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?
Many thanks in advance, Michael
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.
Hi,
I'm not able to get Service Broker to work. I've created the following sample and would excpect to get some data from "PreisanfrageQueue" or "PreisanfrageRequestorQueue". But both they are emtpy.
What do I do wrong?
Regards,
Manfred
create message type Preisanfrage
validation = well_formed_xml;
create message type PreisanfrageAntwort
validation = well_formed_xml;
create contract PreisanfrageContract
(
Preisanfrage sent by initiator,
PreisanfrageAntwort sent by target
);
create queue PreisanfrageRequestorQueue with
status=on;
create queue PreisanfrageQueue;
create service PreisanfrageRequestorService
on queue PreisanfrageRequestorQueue ( PreisanfrageContract );
create service PreisanfrageService
on queue PreisanfrageQueue (PreisanfrageContract );
create table debug_table;
create table debug_table (id int primary key identity(1,1), msg varchar(100));
create procedure PreisanfrageAction
as
declare @conversation uniqueidentifier
declare @msg nvarchar(max)
declare @msgType nvarchar(256)
declare @answer xml;
insert into debug_table(msg) values('1');
;receive top(1)
@conversation = conversation_handle,
@msg = message_body,
@msgType = message_type_name
from PreisanfrageQueue;
insert into debug_table(msg) values('2');
-- Preisanfrage bearbeiten
set @answer = '<preis>1</preis>';
;send on conversation @conversation
message type PreisanfrageAntwort (@answer);
end conversation @conversation;
insert into debug_table(msg) values('3');
alter queue PreisanfrageQueue
with
status=on,
activation (
status=on,
PROCEDURE_NAME = PreisanfrageAction,
max_queue_readers = 100,
EXECUTE AS OWNER
);
-- Dialog starten
declare @conversation uniqueidentifier;
begin dialog conversation @conversation
from service [PreisanfrageRequestorService]
to service 'PreisanfrageService'
on contract [PreisanfrageContract];
declare @request xml;
set @request = '<?xml version="1.0" encoding="UTF-8"?><Preisanfrage xmlns="4711101'">http://www.xyz.at/samples/Preisanfrage"><KundenId>4711</KundenId><ProduktId>10</ProduktId><Anzahl>1</Anzahl></Preisanfrage>';
;send on conversation @conversation
message type Preisanfrage ( @request );
receive * from PreisanfrageQueue;
receive * from PreisanfrageRequestorQueue;
select * from debug_table
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!
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.
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
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
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
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