Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Internal Activation Procedure Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2014 2:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 3, Visits: 27
Hi,
I having some issues getting Service Broker to work with an Internal Activation Procedure.

I have the following code.
USE [CabinetDoor]
GO

--ALTER DATABASE CabinetDoor SET ENABLE_BROKER;

IF EXISTS (SELECT * FROM sys.services WHERE name = N'WFProductionInProcess_Target')
BEGIN
DROP SERVICE
[WFProductionInProcess_Target];
END

IF EXISTS (SELECT * FROM sys.services WHERE name = N'WFProductionSchedule_Initiator')
BEGIN
DROP SERVICE
[WFProductionSchedule_Initiator];
END

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'WFPIPScheduleInitiatorQueue')
BEGIN
DROP QUEUE WFPIPScheduleInitiatorQueue;
END

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'WFPIPScheduleTargetQueue')
BEGIN
DROP QUEUE WFPIPScheduleTargetQueue;
END

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'WFPIPContract')
BEGIN
DROP CONTRACT
[WFPIPContract];
END

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'WFPIPRequestMessage')
BEGIN
DROP MESSAGE TYPE
[WFPIPRequestMessage];
END

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'WFPIPReplyMessage')
BEGIN
DROP MESSAGE TYPE
[WFPIPReplyMessage];
END

GO

--Create Message Type
CREATE MESSAGE TYPE [WFPIPRequestMessage]
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [WFPIPReplyMessage]
VALIDATION = WELL_FORMED_XML;

GO

--Create Contract
CREATE CONTRACT [WFPIPContract]
(
[WFPIPRequestMessage] SENT BY INITIATOR
, [WFPIPReplyMessage] SENT BY TARGET
);

GO

--Create Queue
CREATE QUEUE WFPIPScheduleTargetQueue
WITH STATUS=ON, ACTIVATION
(STATUS=ON, MAX_QUEUE_READERS=5,
PROCEDURE_NAME = BrokerTargetActiveProc, EXECUTE AS SELF),
POISON_MESSAGE_HANDLING (STATUS=OFF);

CREATE SERVICE [WFProductionInProcess_Target]
ON QUEUE WFPIPScheduleTargetQueue (WFPIPContract);

CREATE QUEUE WFPIPScheduleInitiatorQueue

--Create WFProductionSchedule_Initiator
CREATE SERVICE WFProductionSchedule_Initiator
ON QUEUE WFPIPScheduleInitiatorQueue;


Post #1583426
Posted Wednesday, June 18, 2014 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 3, Visits: 27
This forum will not allow me to post all my code, so I have attach it as files.

I call the code in Start Conversation from a desktop application, using integrated security.

I have implemented one other service broker project, and I remember doing some security changes to allow the queue to receive and send and such. Is this all I am missing?

I have followed several tutorials without any luck.

Thanks
Gary


  Post Attachments 
Setup.txt (1 view, 1.91 KB)
Activation SP.txt (1 view, 1.42 KB)
Start Conversation.txt (1 view, 789 bytes)
Post #1583433
Posted Saturday, June 21, 2014 11:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
There are many reasons why Service Broker can fail.

For analyzing this I recommend to disable the activation procedure. This way you can see if the data is at least reaching the target queue.
If so, call the activation procedure manually and have a look what it's doing.

Have a look at the command line utility "ssbdiagnose" (http://msdn.microsoft.com/en-us/library/bb934450.aspx)
It sends a test package between defined services. It helped me a lot getting Service Broker run.
Post #1584685
Posted Saturday, June 21, 2014 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 3, Visits: 27
Hi,

Thanks for replying.

I ended up following another tutorial, that defined all stored procedure parts gradually. I think it was Remus somebody, basically testing without the activation turned on.

Thanks
Gary
Post #1584758
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse