May 5, 2011 at 7:51 am
I restored a copy of the production database (ACCMAIN) as new database (TARPIT)
I'm trying to setup a service broker which will enable the our different apps to communicate via SQL.
I have done the following:
[font="Courier New"]USE [master];
ALTER DATABASE TARPIT SET TRUSTWORTHY ON;
USE TARPIT;
CREATE MESSAGE TYPE msgExecBox
VALIDATION = NONE;
CREATE CONTRACT cntExecBox
(msgExecBox SENT BY ANY);
CREATE QUEUE queExecBoxSnd
WITH
STATUS=ON
,RETENTION=OFF;
CREATE QUEUE queExecBoxRec
WITH
STATUS=ON
,RETENTION=OFF;
CREATE SERVICE serExecBoxSnd
ON QUEUE queExecBoxSnd
(cntExecBox);
CREATE SERVICE serExecBoxRec
ON QUEUE queExecBoxRec
(cntExecBox);
CREATE ROUTE rteExecBox
WITH
SERVICE_NAME = 'serExecBoxRec',
ADDRESS = 'LOCAL'[/font]
Now all I supposedly have to do is run the following code:
[font="Courier New"]DECLARE @handle uniqueidentifier
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE serExecBoxSnd
TO SERVICE 'serExecBoxRec'
ON CONTRACT cntExecBox;
--Sends a message
SEND ON CONVERSATION @handle
MESSAGE TYPE msgExecBox
('hello world')
--And ends the conversation
END CONVERSATION @handle WITH CLEANUP;
SELECT * FROM queExecBoxRec;[/font]
But there are no messages in the QUEUE when I run the select statement.
Thing to keep in mind:
- The database was restored from a backup of production database.
- I just renamed the all the entities to fit in with our standards.
- I did not run "CREATE MASTER KEY ENCRYPTION BY PASSWORD" as shown in the example.
Can anyone spot any problem with what I've done and maybe help me out.
Thanks
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
May 6, 2011 at 1:30 am
All the examples I saw so far creates a new database. I have not seen ONE example where they show you how to setup up the service broker on an existing database.
:unsure:
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
May 9, 2011 at 3:11 am
Ok so here are a few solutions I found all on my own.
Make sure that:
1. the BrokerID for the databases on the instance are different
2. the Broker is enabled.
3. the Trustworthy setting is set to true.
USE THE FOLLOWING TO VIEW THE SETTING FOR EACH DATABASE
[font="Courier New"]SELECT [name], is_broker_enabled, service_broker_guid, is_trustworthy_on
FROM sys.databases[/font]
Ownership:
If the ownership saved in the master database for your database diifers from what is set in the database itself, that can cuase a huge issue for Service Broker
USE THE FOLLOWING LINK TO SEE HOW THIS CAN BE FIXED WITH ALTER AUTHORIZATION
I have after 5 brutal and almost suicidal days solved the issue. If you need any other info let me know.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply