I have two databases DB1 and DB2. Bothe the databases are on the same SQL
instance. Both have Service Broker enabled.
I am trying to send message from DB1 to DB2 using the following code.
On DB1, I did the following:
create queue TestStart
create message type MsgTypeNone
create contract TestSentyAny (MsgTypeNone SENT BY ANY)
create service SrvcResponse on queue TestStart (TestSentByAny)
On DB2, I did the following:
create queue Rcvr
create message type MsgTypeNone
create contract TestSentyAny (MsgTypeNone SENT BY ANY)
create service SndrToMsgRcvr on queue Rcvr (TestSentByAny)
Then, I am trying to send the message from DB1.
use DB1;
declare @ConvHndl uniqueidentifier, @SrvcBrkrID uniqueidentifier;
select @SrvcBrkrID = service_broker_guid from sys.databases where
database_id = DB_ID('DB2');
begin dialog @ConvHndl from service SrvcResponse to service
'SndrToMsgRcvr', @SrvcBrkrID on contract TestSentyAny with encryption=off,
lifetime=20;
print @ConvHndl
send on conversation @ConvHndl message type MsgTypeNone('Test message from
DB1');
----------
There are no error when the above is executed.
I do get a valid @ConvHndl.
I checked the queue Rcvr, using select * from Rcvr on DB2, but the message
is not there.
Instead, I get the error, which is stored in TestStart queue in DB1. It
says: "The dialog has exceeded the specified lifetime." I believe 20 seconds
is a lot of time.
I think something else is not done right. Possibly, SndrToMsgRcvr service in
DB2 is not accepting messages from another service in a different DB.
I do have other services in both DB1 and DB2. I am able to put messages from
one service to another service in the SAME DB. But things are not working
across DBs.
Could somebody help in resolving this issue?
Thanks
HB - 06 Jan 2006 02:21 GMT
I ran the profiler and some more info to include.
This message could not be delivered because an internal error (code 916,
state 3) was encountered while processing it. Check the error log for more
information.
I checked the ERROR.LOG file. There is no additional information.
> I have two databases DB1 and DB2. Bothe the databases are on the same SQL
> instance. Both have Service Broker enabled.
[quoted text clipped - 51 lines]
>
> Thanks
Bob Beauchemin - 06 Jan 2006 03:32 GMT
You need to mark the initiator database as TRUSTWORTHY with ALTER DATABASE.
Note that this is not the preferred method. The preferred method is to set
up dialog security and encryption. I'm assuming that you're just trying this
as a test to use the least possible keystrokes. I've written up a few blog
entries on this at the address below.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
>I ran the profiler and some more info to include.
>
[quoted text clipped - 65 lines]
>>
>> Thanks