Prev: Soundex Problem
Next: Scope Variables and SSIS Sequence Containers
From: Markus Kropik on 9 Jun 2010 10:34
I desperately need help with service broker. I have created a simple
application (code below), which uses a message queue to send messages from
the initiator to the target. No response is required (I need a fire and
forget strategy here).

Although both sides of the conversation call END CONVERSATION, the table
sys.conversation_endpoints is not purged and keeps filling up forever. SQL
Server books online recommend using END CONVERSATION WITH CLEANUP for
administrative purposes only. Can anyone please show me a way to properly end
conversations in a "normal" way? I have not been able to find one.

Code follows:

USE [master]
GO
/****** Object: Database [BrokerTest] Script Date: 06/09/2010 16:03:27
******/
CREATE DATABASE [BrokerTest] ON PRIMARY
( NAME = N'BrokerTest', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\BrokerTest.mdf' , SIZE = 9920KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'BrokerTest_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\BrokerTest_log.ldf' , SIZE = 16576KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [BrokerTest] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [BrokerTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [BrokerTest] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [BrokerTest] SET ANSI_NULLS OFF
GO
ALTER DATABASE [BrokerTest] SET ANSI_PADDING OFF
GO
ALTER DATABASE [BrokerTest] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [BrokerTest] SET ARITHABORT OFF
GO
ALTER DATABASE [BrokerTest] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [BrokerTest] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [BrokerTest] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [BrokerTest] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [BrokerTest] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [BrokerTest] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [BrokerTest] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [BrokerTest] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [BrokerTest] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [BrokerTest] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [BrokerTest] SET ENABLE_BROKER
GO
ALTER DATABASE [BrokerTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [BrokerTest] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [BrokerTest] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [BrokerTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [BrokerTest] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [BrokerTest] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [BrokerTest] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [BrokerTest] SET READ_WRITE
GO
ALTER DATABASE [BrokerTest] SET RECOVERY FULL
GO
ALTER DATABASE [BrokerTest] SET MULTI_USER
GO
ALTER DATABASE [BrokerTest] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [BrokerTest] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'BrokerTest', N'ON'
GO
USE [BrokerTest]
GO
/****** Object: StoredProcedure [dbo].[ReadMe] Script Date: 06/09/2010
16:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReadMe]
AS
BEGIN
SET NOCOUNT ON;

PRINT 'Tut nix, speichert nur Doku in der Datenbank'

/*

exec dbo.RaiseTestMessage 'Type1', '<test>Message</test>'
select queuing_order, service_name, message_body = CAST(message_body as xml)
from dbo.TestMessageQueue where validation = 'X'
select * from dbo.MsgDebug order by id

ALTER QUEUE [dbo].[TestMessageQueue] WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[TestMessageQueueHandler]
, MAX_QUEUE_READERS = 3 , EXECUTE AS N'dbo' )
ALTER QUEUE [dbo].[TestMessageQueue] WITH STATUS = ON , RETENTION = OFF ,
ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = [dbo].[TestMessageQueueHandler]
, MAX_QUEUE_READERS = 3 , EXECUTE AS N'dbo' )
truncate table dbo.MsgDebug


exec dbo.BatchRaiseTestMessage 3

*/

END
GO
/****** Object: StoredProcedure [dbo].[RaiseTestMessage] Script Date:
06/09/2010 16:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RaiseTestMessage]
@Name NVARCHAR(32),
@DataBlock XML = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE @dlg UNIQUEIDENTIFIER,
@x XML;

SET @x = CASE WHEN @DataBlock is null
THEN '<TestMessage/>'
ELSE '<TestMessage>' + cast(@DataBlock as nvarchar(max)) +
'</TestMessage>'
END;

SET @x.modify(N'insert <name>{sql:variable("@Name")}</name> into
/TestMessage[1]');

print 'Sending message: ' + cast(@x as nvarchar(max));

BEGIN DIALOG @dlg
FROM SERVICE TestMessageProducer
TO SERVICE 'TestMessageConsumer'
ON CONTRACT TestMessageContract
WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @dlg MESSAGE TYPE TestMessageType(@x);

END CONVERSATION @dlg;
END
GO
/****** Object: Table [dbo].[MsgDebug] Script Date: 06/09/2010 16:03:30
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MsgDebug](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[dt] [datetime] NOT NULL,
[c_handle] [uniqueidentifier] NULL,
[m_body] [xml] NULL,
[m_type] [nvarchar](128) NULL,
[a_id] [int] NULL,
CONSTRAINT [PK_MsgDebug] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[TestMessageQueueHandler] Script
Date: 06/09/2010 16:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------------------
-- set up event queue handler
----------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[TestMessageQueueHandler]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @conversation_handle UNIQUEIDENTIFIER,
@message_body XML,
@message_type_name NVARCHAR(128);

RECEIVE TOP(1)
@conversation_handle = conversation_handle,
@message_type_name = message_type_name,
@message_body = (CASE WHEN validation = 'X'
THEN CAST(message_body as XML)
ELSE NULL
END)
FROM dbo.TestMessageQueue;

IF @@ROWCOUNT = 0
RETURN;

IF @message_type_name IN
(N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
INSERT dbo.MsgDebug (a_id, c_handle, m_body, m_type, dt)
VALUES (0, @conversation_handle, 'ending dialog', @message_type_name,
GETDATE());

END CONVERSATION @conversation_handle;
RETURN;
END

-- extract message parameters
INSERT dbo.MsgDebug (a_id, c_handle, m_body, m_type, dt)
VALUES (0, @conversation_handle, @message_body, @message_type_name,
GETDATE());

END CONVERSATION @conversation_handle;
END
GO
/****** Object: StoredProcedure [dbo].[BatchRaiseTestMessage] Script
Date: 06/09/2010 16:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BatchRaiseTestMessage]
@BatchSize INT = 1
AS
BEGIN
SET NOCOUNT ON;

DECLARE @cnt INT, @name NVARCHAR(64), @x XML
SET @cnt = 0
WHILE @cnt < @BatchSize
BEGIN
SET @cnt = @cnt + 1
SET @name = 'Name ' + CAST(@cnt AS NVARCHAR)
SET @x = '<test>Message ' + CONVERT(NVARCHAR, GETDATE(), 114) + '</test>'
EXEC dbo.RaiseTestMessage @name, @x
END
END
GO

 | 
Pages: 1
Prev: Soundex Problem
Next: Scope Variables and SSIS Sequence Containers