Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Service Broker / January 2006

Tip: Looking for answers? Try searching our database.

Send message from service in one DB to service in another DB

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
HB - 06 Jan 2006 02:09 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.