Problem Service Broker

How Can I Trace Follw Code ?

(don't Trace in Visual Studio 2005)

Follow Code Run On a Sql Server 2005 Correctly But On Another Sql Server 2005 don't Run Correctly .

Please Help Me For Solve This Problem.

Thanks.

USEMaster

ALTERDatabase AdventureWorks

SETENABLE_BROKER

ALTERDatabase AdventureWorks

SETNEW_BROKER

USE AdventureWorks

GO

DROPMASTERKEY

CREATEMASTERKEYENCRYPTIONBYPASSWORD='P@ssw0rd'

IFEXISTS(SELECTobject_idFROMsys.proceduresWHEREname='usp_RequestHoliday')

DROPPROCEDURE usp_RequestHoliday;

IFEXISTS(SELECTobject_idFROMsys.proceduresWHEREname='usp_ProcessHolidayRequest')

DROPPROCEDURE usp_ProcessHolidayRequest;

IFEXISTS(SELECT service_idFROMsys.servicesWHEREname='http://schemas.apress.com/prosqlserver/HolidayRequestProcessorService')

DROPSERVICE [http://schemas.apress.com/prosqlserver/HolidayRequestProcessorService];

IFEXISTS(SELECT service_idFROMsys.servicesWHEREname='http://schemas.apress.com/prosqlserver/HolidayRequestInitiatorService')

DROPSERVICE [http://schemas.apress.com/prosqlserver/HolidayRequestInitiatorService];

IFEXISTS(SELECTobject_idFROMsys.service_queuesWHEREname='HolidayRequestQueue')

DROPQUEUE HolidayRequestQueue;

IFEXISTS(SELECT service_contract_idFROMsys.service_contractsWHEREname='http://schemas.apress.com/prosqlserver/HolidayRequestContract')

DROPCONTRACT [http://schemas.apress.com/prosqlserver/HolidayRequestContract];

IFEXISTS(SELECT message_type_idFROMsys.service_message_typesWHEREname='http://schemas.apress.com/prosqlserver/HolidayRequest')

DROPMESSAGETYPE [http://schemas.apress.com/prosqlserver/HolidayRequest];

IFEXISTS(SELECT message_type_idFROMsys.service_message_typesWHEREname='http://schemas.apress.com/prosqlserver/SendFromTarget')

DROPMESSAGETYPE [http://schemas.apress.com/prosqlserver/SendFromTarget];

IFEXISTS(SELECT xml_collection_idFROMsys.xml_schema_collectionsWHEREname='http://schemas.apress.com/prosqlserver/HolidayRequestSchema')

DROPXMLSCHEMACOLLECTION [http://schemas.apress.com/prosqlserver/HolidayRequestSchema];

CREATEXMLSCHEMACOLLECTION [http://schemas.apress.com/prosqlserver/HolidayRequestSchema]

AS N'<?xml version="1.0" encoding="utf-16"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="vacationRequest">

<xs:complexType>

<xs:sequence minOccurs="1" maxOccurs="1">

<xs:element name="employeeId" type="xs:integer" />

<xs:element name="email" type="xs:string" />

<xs:element name="startTime" type="xs:dateTime" />

<xs:element name="hours" type="xs:integer" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>';

CREATEMESSAGETYPE [http://schemas.apress.com/prosqlserver/HolidayRequest]

VALIDATION=VALID_XMLWITHSCHEMACOLLECTION [http://schemas.apress.com/prosqlserver/HolidayRequestSchema];

CREATEMESSAGETYPE [http://schemas.apress.com/prosqlserver/SendFromTarget]

VALIDATION=VALID_XMLWITHSCHEMACOLLECTION [http://schemas.apress.com/prosqlserver/HolidayRequestSchema];

CREATECONTRACT [http://schemas.apress.com/prosqlserver/HolidayRequestContract]

[http://schemas.apress.com/prosqlserver/HolidayRequest] SENTBY INITIATOR,

[http://schemas.apress.com/prosqlserver/SendFromTarget] SENTBY Target

CREATEPROCEDURE usp_ProcessHolidayRequest

DECLARE @msgBodyXML([http://schemas.apress.com/prosqlserver/HolidayRequestSchema]),

@convIDuniqueidentifier,

@emailvarchar(50),

@employeeIDint,

@hoursint,

@startTimeDateTime,

@hoursTakenint,

@msgTypenvarchar(256),

@ServiceNamenvarchar(256),

@Statustinyint;

DECLARE @msgTableTABLE

message_bodyvarbinary(max),

conversation_handleuniqueidentifier,

message_type_namenvarchar(256),

Service_Namenvarchar(256),

Statustinyint

WAITFOR

(

RECEIVETOP(1) message_body,conversation_handle, message_type_name,Service_Name,Status

FROM HolidayRequestQueue

INTO @msgTable

),TIMEOUT 5000;

SET @msgBody=(SELECTTOP(1)CAST(message_bodyASXML)FROM @msgTable);

SET @convID=(SELECTTOP(1)conversation_handleFROM @msgTable);

SET @msgType=(SELECTTOP(1) message_type_nameFROM @msgTable);

SET @ServiceName=(SELECTTOP(1)Service_NameFROM @msgTable);

SET @Status=(SELECTTOP(1)StatusFROM @msgTable);

--END CONVERSATION @convID;

INSERTINTO dbo.Vacation_Messages(ConvID, MsgType, MsgBody,Service_Name,Status)

VALUES(@ConvID, @MsgType, @MsgBody,@ServiceName,@Status);

DECLARE

@body1nvarchar(1000),

@msg1xml;

SET @body1= N'<?xml version="1.0"?>

<vacationRequest>

<employeeId>'+'1222'+'</employeeId>

<email>'+'Test@yahoo.com'+'</email>

<startTime>'+'2005-11-01T09:00:00+00:00'+'</startTime>

<hours>'+'9'+'</hours>

</vacationRequest>';

SET @msg1=CAST(@body1ASXML);

SENDONCONVERSATION @convID

MESSAGETYPE [http://schemas.apress.com/prosqlserver/SendFromTarget]

(@msg1);

Print'target send'

END-- End Procedure

CREATEQUEUE HolidayRequestQueue

STATUS=ON,

RETENTION=ON,

(

STATUS=ON,

PROCEDURE_NAME= usp_ProcessHolidayRequest,

MAX_QUEUE_READERS= 5,

EXECUTEASSELF

);

CREATESERVICE [http://schemas.apress.com/prosqlserver/HolidayRequestProcessorService]

ONQUEUE HolidayRequestQueue

CREATESERVICE [http://schemas.apress.com/prosqlserver/HolidayRequestInitiatorService]

ONQUEUE HolidayRequestQueue

CREATEPROCEDURE usp_RequestHoliday

@employeeIdint,

@emailvarchar(50),

@hoursint,

@startDatevarchar(50)

DECLARE @dialogHandleuniqueidentifier,

@bodynvarchar(1000),

@msgXML,

@datenvarchar(100)

SET @body= N'<?xml version="1.0"?>

<vacationRequest>

<employeeId>'+CAST(@employeeIDASvarchar)+'</employeeId>

<email>'+ @email+'</email>

<startTime>'+ @startDate+'</startTime>

<hours>'+CAST(@hoursASnvarchar)+'</hours>

</vacationRequest>';

SET @msg=CAST(@bodyASXML)

BEGINDIALOGCONVERSATION @dialogHandle

FROMSERVICE

TOSERVICE

'http://schemas.apress.com/prosqlserver/HolidayRequestProcessorService'

ONCONTRACT

[http://schemas.apress.com/prosqlserver/HolidayRequestContract];

SENDONCONVERSATION @dialogHandle

MESSAGETYPE [http://schemas.apress.com/prosqlserver/HolidayRequest]

(@msg);

--END CONVERSATION @dialogHandle;

Print'Request sent!'

EXEC usp_RequestHoliday 140,'someone@somewhere.com', 8,'2005-11-01T09:00:00+00:00'

SELECT S.nameASService, Q.nameASQueue

FROMsys.servicesAS S

JOINsys.service_queuesAS Q

ON Q.object_id= S.service_queue_id

GO

SELECT*FROMsys.conversation_endpoints

SELECT from_service_name,

to_service_name,

FROMsys.transmission_queue

SELECT*

FROM HolidayRequestQueue

SELECTStatus,queuing_order,CONVERT(XML,message_body)as Message1,Service_Name

FROM HolidayRequestQueue

[35451 byte] By [MS_PowerUser] at [2007-12-31]