#眉標=SQL Server 2008、Service Broker、Message
#副標=實用SQL Server 2008（5）
#大標=資料庫訊息交換平台Service Broker(下)
#作者=文／圖		胡百敬



==<box>===========
程式1

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(1000);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;
--接收第一筆訊息，等待一秒鐘，若沒有任何訊息就離開
  WAITFOR
   ( RECEIVE TOP(1)
	@RecvReqDlgHandle = conversation_handle,
	@RecvReqMsg = message_body,
	@RecvReqMsgName = message_type_name
	FROM TargetQueue
   ), TIMEOUT 1000;

--檢視所接收到的訊息內容
  SELECT @RecvReqMsg AS ReceivedRequestMsg;

-- 判斷訊息類型，是否是需要處理的
-- 此處為了簡單示範，僅處理我們自行發出的訊息
  IF @RecvReqMsgName = N'EventMessage'
   BEGIN
	--在對話上回送兩個訊息
 	--一是自訂的<ReplyMsg>已經收到事件資料</ReplyMsg>
	--另一是END CONVERSATION時，系統預定的狀態訊息
	DECLARE @ReplyMsg NVARCHAR(100);
	SET @ReplyMsg =
	N'<ReplyMsg>已經收到事件資料</ReplyMsg>';
	SEND ON CONVERSATION @RecvReqDlgHandle
	  MESSAGE TYPE [ReplyEventMessage]
	  (@ReplyMsg);
	-- 結束對話
	END CONVERSATION @RecvReqDlgHandle;
  END
COMMIT TRANSACTION;
==<end>==============





===<反灰>=============
ALTER QUEUE dbo.TargetQueue WITH RETENTION = ON
RECEIVE TOP(1) * FROM TargetQueue
SELECT * FROM dbo.TargetQueue
==<end>==============




===<反灰>=============
RECEIVE * FROM dbo.InitiatorQueue
WHERE conversation_handle='DB517FE2-3148-DE11-B3E6-001C258B4E98'
==<end>==============




===<反灰>=============
訊息8426，層級16，狀態20，行1
找不到交談控制代碼"DB517FE2-3148-DE11-B3E6-001C258B4E98"。
陳述式已經結束。
==<end>==============




==<box>===========
程式2

--在本機建立資料表，存放所觸發事件的記錄
--以驗證相關訊息是否已經傳送到目的端
CREATE TABLE tbLog(
PK INT IDENTITY(1,1) PRIMARY KEY,
orgEventData XML)
GO

--建立DDL Trigger，在事件發生時，
--透過Service Broker，將事件資料送往集中的資料庫
CREATE TRIGGER trg ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN TRY
  DECLARE @RequestMsg XML;
  SELECT @RequestMsg = EVENTDATA();
--在本機記錄事件，除錯時好比對
  INSERT tbLog(orgEventData) VALUES(@RequestMsg);

--寄發訊息
  BEGIN TRANSACTION
	DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

	BEGIN DIALOG @InitDlgHandle
	 FROM SERVICE [EventInitiatorService]
	 TO SERVICE N'EventTargetService'
	 ON CONTRACT [EventCollectContract]
	 WITH
	  ENCRYPTION = ON;

	 SEND ON CONVERSATION @InitDlgHandle
	 MESSAGE TYPE [EventMessage]
	 (@RequestMsg);
	COMMIT TRAN
END TRY
BEGIN CATCH
	IF XACT_STATE()<>0 ROLLBACK TRAN
END CATCH
==<end>==============




===<反灰>=============
--建立與刪除物件，以觸發DDL Trigger
create table t(c1 int)
drop table t

--觀察系統的執行狀況
select * from tbLog
==<end>==============






==<box>===========
程式3

--建立存放通知的資料表，如果資料表已經存在就先刪除
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=Object_id(N'dbo.AuditLog') AND OBJECTPROPERTY(id,N'IsTable')=1)
  DROP TABLE dbo.AuditLog
GO
CREATE TABLE AuditLog
(
--故意讓欄位長度不足，形成之後要討論的毒訊息
--接下來的錯誤處理與監控會討論此一部分
Command NVARCHAR(10),  --正常或許該設成 1000
PostTime NVARCHAR(24),
HostName Nvarchar(100),
LoginName Nvarchar(100),
OrgEventData XML
)
GO
CREATE TABLE tbErrorLog
(
  PK INT IDENTITY(1,1) PRIMARY KEY,
  ErrorProcedure SYSNAME,
  ErrorLine	INT,
  ErrorNumber INT,
  ErrorMessage NVARCHAR(1000),
  ErrorSeverity TINYINT,
  EventTime DATETIME2(3) DEFAULT(SYSDATETIME())	
)
==<end>==============





==<box>===========
程式4

--處理因為事件通知而寄發到佇列的訊息，
--將結果放入到先前建立的AuditLog 資料表
CREATE PROC spProcessMsg 
AS
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @messageBody XML;
DECLARE @RecvReqMsgName sysname;

BEGIN TRY
BEGIN TRANSACTION;
  WAITFOR
  ( RECEIVE TOP(1)
　　@RecvReqDlgHandle = conversation_handle,
　　@messageBody = message_body,
　　@RecvReqMsgName = message_type_name
  FROM TargetQueue
  ), TIMEOUT 1000;

  IF @RecvReqMsgName = N'EventMessage'
  BEGIN
	END CONVERSATION @RecvReqDlgHandle;
	    
	--宣告承接各種資料的變數
	DECLARE @cmd NVARCHAR(1000)
	DECLARE @PostTime NVARCHAR(24)
	DECLARE @Hostname NVARCHAR(100)
	DECLARE @LoginName NVARCHAR(100)

	--以XQuery 取得事件資料細節
	SET @cmd=@messageBody.value(
	'data(//TSQLCommand//CommandText)[1]','NVARCHAR(1000)')
	SET @PostTime=@messageBody.value(
	'data(//PostTime)[1]','NVARCHAR(24)')
	SET @HostName=@messageBody.value(
	'data(//ServerName)[1]','SYSNAME')
	SET @LoginName=@messageBody.value(
	'data(//LoginName)[1]','NVARCHAR(50)')

	--資料長度太長，而欄位長度不足
	INSERT INTO AuditLog(Command,PostTime,HostName,LoginName,OrgEventData) 
	VALUES(@cmd,@PostTime,@HostName,@LoginName,@messageBody)
  END
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
  IF XACT_STATE()<> 0 ROLLBACK TRAN
  INSERT tbErrorLog(
  ErrorProcedure,	ErrorLine,	ErrorNumber,
  ErrorMessage, ErrorSeverity)	
  VALUES(ERROR_PROCEDURE(), ERROR_LINE(),
  ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY())
END CATCH
go
==<end>==============






==<box>===========
程式5

ALTER QUEUE dbo.TargetQueue 
  WITH STATUS = ON,
  ACTIVATION (
  STATUS=OFF,
  PROCEDURE_NAME = dbo.spProcessMsg,
  MAX_QUEUE_READERS = 1,
  EXECUTE AS SELF)
GO

select * from TargetQueue

--若沒有啟動由佇列自動呼叫預存程序
--自行呼叫處理佇列的預存程序
EXEC spProcessMsg

SELECT * FROM AuditLog
==<end>==============





===<反灰>=============
select *,CONVERT(nvarchar(max),Message_body) from InitiatorQueue
--手動結束掉某個對話
END CONVERSATION '<上述查詢中所獲得的交談控制代碼>'
==<end>==============





==<box>===========
程式6

DECLARE @handle UNIQUEIDENTIFIER
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT conversation_handle FROM dbo.InitiatorQueue
WHERE message_type_name='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
OPEN cur
FETCH NEXT FROM cur INTO @handle
DECLARE @sql VARCHAR(200)
WHILE @@FETCH_STATUS=0
BEGIN
  SET @sql='END CONVERSATION ''' + CONVERT(CHAR(36),@handle) + ''''
  EXEC(@sql)
  FETCH NEXT FROM cur INTO @handle
END
CLOSE cur
DEALLOCATE cur
==<end>==============






===<反灰>=============
ssbdiagnose -E CONFIGURATION FROM SERVICE EventInitiatorService -S ByronSub -d InitiatorDB TO SERVICE EventTargetService -S SQL2K8 -d TargetDB ON CONTRACT EventCollectContract
==<end>==============




===<反灰>=============
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHT/s10sb_5techref/html/0c1636e8-a3db-438e-be4c-1ea40d1f4877.htm
==<end>==============




===<反灰>=============
SELECT * FROM sys.transmission_queue  FOR XML AUTO,ELEMENTS ,BINARY BASE64
==<end>==============





==<box>===========
程式7

<sys.transmission_queue>
<conversation_handle>1B488664-3B48-DE11-B3E6-001C258B4E99</conversation_handle>
  <to_service_name>EventTargetService</to_service_name>
  <to_broker_instance></to_broker_instance>
  <from_service_name>EventInitiatorService</from_service_name>
  <service_contract_name>EventCollectContract</service_contract_name>
  <enqueue_time>2009-05-24T08:18:00.450</enqueue_time>
  <message_sequence_number>0</message_sequence_number>
  <message_type_name>EventMessage</message_type_name>
  <is_conversation_error>0</is_conversation_error>
  <is_end_of_dialog>0</is_end_of_dialog>
  <message_body>//48AEUAV … A==</message_body>
  <transmission_status></transmission_status>
  <priority>3</priority>
</sys.transmission_queue>
==<end>==============




===<反灰>=============
SELECT * FROM sys.service_queues WHERE NAME='TargetQueue' FOR XML AUTO,ELEMENTS
==<end>==============





==<box>===========
程式8

<sys.service_queues>
  <name>TargetQueue</name>
  <object_id>2105058535</object_id>
  <schema_id>1</schema_id>
  <parent_object_id>0</parent_object_id>
  <type>SQ</type>
  <type_desc>SERVICE_QUEUE</type_desc>
  <create_date>2009-05-09T14:07:24.350</create_date>
  <modify_date>2009-05-24T16:17:28.913</modify_date>
  <is_ms_shipped>0</is_ms_shipped>
  <is_published>0</is_published>
  <is_schema_published>0</is_schema_published>
  <max_readers>1</max_readers>
  <activation_procedure>[dbo].[spProcessMsg]</activation_procedure>
  <execute_as_principal_id>1</execute_as_principal_id>
  <is_activation_enabled>1</is_activation_enabled>
  <is_receive_enabled>0</is_receive_enabled>
  <is_enqueue_enabled>0</is_enqueue_enabled>
  <is_retention_enabled>1</is_retention_enabled>
</sys.service_queues>
==<end>==============


若你要實做Event Notification機制搭配Service Broker將事件訊息傳到遠端機器，可以參考線上說明：ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHT/s10de_4deptrbl/html/12afbc84-2d2a-4452-935e-e1c70e8c53c1.htm。