service broker - message could not delivered because the service contract cannot be found

• invaliddba

  Default port

  Points: 1450

  More actions

  April 17, 2011 at 6:43 pm

  #234914

  I am using service broker between two databases on the same server.

  The messages are stuck at the sending queue .

  The error is sql profiler is " This message could not be delivered because the service contract could not found . :Service contract <name>'

  There are no messages in the receiving queue and sys.transmission_queue.

  Here is the script I am using :

  create database sb1

  with trustworthy on

  create database sb2

  with trustworthy on

  use sb1

  alter database sb1 set enable_broker

  use sb2

  alter database sb2 set enable_broker

  create master key encryption by password='p@ssw0rd'

  use sb2

  create master key encryption by password='p@ssw0rd'

  use sb1

  create message type mysendmsg

  validation=well_formed_xml

  create message type myrecvmsg

  validation=well_formed_xml

  use sb2

  create message type mysendmsg

  validation=well_formed_xml

  create message type myrecvmsg

  validation=well_formed_xml

  --create contract

  use sb1

  create contract mysb1contract1

  (mysendmsg sent by initiator,

  myrecvmsg sent by target);

  --drop contract mysendcontract

  use sb2

  create contract myrecvcontract

  (mysendmsg sent by initiator,

  myrecvmsg sent by target);

  -- create queue

  use sb1

  create queue sb1queue

  with status =on

  use sb2

  create queue sb2queue

  with status=on

  --create service

  use sb1

  create service servicesb1

  on queue sb1queue (mysb1contract)

  use sb2

  create service servicesb2

  on queue sb2queue (myrecvcontract)

  use sb2

  grant control on service ::servicesb1 to public

  use sb1

  --start conversation

  declare @xmlmsg xml;

  declare @handler uniqueidentifier;

  begin dialog conversation @handler

  from service servicesb1

  to service 'servicesb2'

  on contract mysb1contract1;

  set @xmlmsg='<message>testmsg</message>';

  send on conversation @handler

  message type mysendmsg(@xmlmsg)

  END CONVERSATION @handler with cleanup;

  use sb2

  select * from sb2queue

  select * from sys.transmission_queue

  select db_ID()

  use sb1

  select * from sb1queue

  -- The messages are stuck in sb1queue.

• Nils Gustav Stråbø

  SSChampion

  Points: 11259

  More actions

  April 18, 2011 at 3:40 am

  #1312647

  Contract name must be identical in both databases.

• invaliddba

  Default port

  Points: 1450

  More actions

  April 18, 2011 at 8:43 am

  #1312842

  thanks