Sql Server Broker Starting Conversation but not Passing to remote server

• phillipwatson1976

  Newbie

  Points: 1

  More actions

  August 30, 2011 at 8:30 am

  #256481

  Hi chaps and chappettes.

  I am in the process of configuring a server broker solution between a local and internet based SQL server.

  below is a mountain of SQL i have put together to attempt to make the two servers work together.

  At face values it seems to work, but when I execute the procedure udef_SendPartInfo

  this starts the conversation but does not seem to pass through to the remote server.

  any advice would be greatly received.

  Thanks Phill

  GO

  /****** Object: StoredProcedure [dbo].[udef_SendPartInfo] Script Date: 08/30/2011 14:54:05 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[udef_SendPartInfo]

  (

  @PartCode VARCHAR(16)

  ,@PartDesc VARCHAR(25)

  )

  AS

  BEGIN

  DECLARE @MessageBody XML

  CREATE TABLE #PartTable

  (

  PartCode VARCHAR(20),

  PartDesc VARCHAR(25),

  )

  INSERT INTO #PartTable

  (

  PartCode

  ,PartDesc

  )

  VALUES

  (

  @PartCode

  ,@PartDesc

  )

  SELECT @MessageBody = (SELECT * FROM #PartTable FOR XML PATH ('Params'), TYPE);

  DECLARE @Handle UNIQUEIDENTIFIER;

  BEGIN DIALOG CONVERSATION @Handle

  FROM SERVICE [ASCinit]

  TO SERVICE 'ASCTarget'

  ON CONTRACT [ASC]

  WITH ENCRYPTION = OFF;

  SEND ON CONVERSATION @Handle

  MESSAGE TYPE [DEFAULT](@MessageBody);

  END

  CREATE PROCEDURE [UDEF_ASCinitQueue]

  AS

  --Uncomment and execute further processing

  --DECLARE @message_type_name sysname

  --DECLARE @dialog uniqueidentifier

  --DECLARE @message_body xml

  --

  --WHILE (1 = 1)

  --BEGIN

  --BEGIN TRANSACTION

  --WAITFOR (

  --RECEIVE top(1)

  --@message_type_name = message_type_name,

  --@message_body = message_body,

  --@dialog = conversation_handle

  --FROM [YourQueue]

  --), TIMEOUT 3000

  --

  --IF (@@ROWCOUNT = 0)

  --BEGIN

  --ROLLBACK TRANSACTION

  --BREAK

  --END

  --

  --IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

  --BEGIN

  --END CONVERSATION @dialog

  --END

  --ELSE

  --IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')

  --BEGIN

  --END CONVERSATION @dialog

  --END

  --ELSE

  --IF (@message_type_name = 'YourType')

  --BEGIN

  --handle your message type

  --END

  --COMMIT TRANSACTION

  --END

  CREATE QUEUE [dbo].[ASCinitQueue] WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = [dbo].[UDEF_ASCinitQueue], MAX_QUEUE_READERS = 1, EXECUTE AS SELF) ON [DEFAULT]

  CREATE CONTRACT [ASC] AUTHORIZATION [dbo] ([DEFAULT] SENT BY ANY)

  CREATE SERVICE [ASCinit] AUTHORIZATION [dbo] ON QUEUE [dbo].[ASCinitQueue] ( [ASC] )

  CREATE ROUTE [Quest_3996cf18-cbd9-42d4-9738-b30318f52c49_ASCTarget] WITH SERVICE_NAME = 'ASCTarget', BROKER_INSTANCE = '3996CF18-CBD9-42D4-9738-B30318F52C49', ADDRESS = 'TCP://INETC231:4012'

  CREATE PROCEDURE [UDEF_ASCTargetQueue]

  AS

  --Uncomment and execute further processing

  --DECLARE @message_type_name sysname

  --DECLARE @dialog uniqueidentifier

  --DECLARE @message_body xml

  --

  --WHILE (1 = 1)

  --BEGIN

  --BEGIN TRANSACTION

  --WAITFOR (

  --RECEIVE top(1)

  --@message_type_name = message_type_name,

  --@message_body = message_body,

  --@dialog = conversation_handle

  --FROM [YourQueue]

  --), TIMEOUT 3000

  --

  --IF (@@ROWCOUNT = 0)

  --BEGIN

  --ROLLBACK TRANSACTION

  --BREAK

  --END

  --

  --IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

  --BEGIN

  --END CONVERSATION @dialog

  --END

  --ELSE

  --IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')

  --BEGIN

  --END CONVERSATION @dialog

  --END

  --ELSE

  --IF (@message_type_name = 'YourType')

  --BEGIN

  --handle your message type

  --END

  --COMMIT TRANSACTION

  --END

  CREATE QUEUE [dbo].[ASCTargetQueue] WITH STATUS = ON, RETENTION = OFF, ACTIVATION (STATUS = ON, PROCEDURE_NAME = [dbo].[UDEF_ASCTargetQueue], MAX_QUEUE_READERS = 1, EXECUTE AS SELF) ON [DEFAULT]

  CREATE CONTRACT [ASC] AUTHORIZATION [dbo] ([DEFAULT] SENT BY ANY)

  CREATE SERVICE [ASCTarget] AUTHORIZATION [dbo] ON QUEUE [dbo].[ASCTargetQueue] ( [ASC] )

  CREATE ROUTE [Quest_5015eb4a-f50e-4c76-b2ab-b8e9cb5a75b8_ASCinit] WITH SERVICE_NAME = 'ASCinit', BROKER_INSTANCE = '5015EB4A-F50E-4C76-B2AB-B8E9CB5A75B8', ADDRESS = 'TCP://INETC231:4012'

  GRANT RECEIVE ON [dbo].[ASCinitQueue] TO [dbo]

  GRANT SEND ON SERVICE::[ASCinit] TO [public]

  GRANT RECEIVE ON [dbo].[ASCTargetQueue] TO [dbo]

  GRANT SEND ON SERVICE::[ASCTarget] TO [public]

  DECLARE @dialog_handle UNIQUEIDENTIFIER;

  BEGIN DIALOG @dialog_handle FROM SERVICE [ASCinit] TO SERVICE 'ASCTarget' , '3996CF18-CBD9-42D4-9738-B30318F52C49' ON CONTRACT [ASC] WITH ENCRYPTION = OFF

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 30, 2011 at 2:22 pm

  #1375231

  Maybe Rusanus Blog[/url] can help you to narrow down the issue.

  You'll need to figure out "how far" the message is transferred and work from there.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]