service borker奇怪的问题,最后50分
随便新建一个数据库，执行以下脚本，service borker都能正常运行。
但如果附加的数据库或者是还原的数据库，执行以下脚本，连队列消息都发送不了。会是什么原因？
------------------------------
CREATE TABLE CLICKLOG
(
id INT IDENTITY(1,1),
departure char(3),
destination char(3),
LeaveDate datetime,
returndate datetime,
flightid int,
clickdate datetime,
ipaddress varchar(50)
);
go
CREATE MESSAGE TYPE [RecordClick] VALIDATION = NONE;
CREATE CONTRACT [ClickContract]
(
[RecordClick] SENT BY INITIATOR
);
CREATE QUEUE [ClickQueue];
CREATE SERVICE [ClickService] ON QUEUE [ClickQueue]([ClickContract]);
GO
CREATE QUEUE [RecordClickQueue];
CREATE SERVICE [RecordClickService] ON QUEUE [RecordClickQueue];
go
---------------------
----------------------
CREATE PROCEDURE clicklogadditem
AS
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
DECLARE @MessageType SYSNAME;
DECLARE @Message XML ;
DECLARE@Departure char(3);
DECLARE@Destination char(3);
DECLARE@LeaveDate datetime;
DECLARE@ReturnDate datetime;
DECLARE@Flightid int;
DECLARE@clickDate datetime ;
DECLARE@IpAddress varchar(50);
RECEIVE TOP (1)
@Handle = conversation_handle,
@MessageType = message_type_name,
@Message = message_body
FROM [ClickQueue];
IF(@Handle IS NOT NULL AND @Message IS NOT NULL)
BEGIN
SELECT @Departure = CAST(CAST(@Message.query( '/Params/Departure/text() ') AS NVARCHAR(MAX)) AS char(3))
SELECT @Destination = CAST(CAST(@Message.query( '/Params/Destination/text() ') AS NVARCHAR(MAX)) AS char(3))
SELECT @LeaveDate = CAST(CAST(@Message.query( '/Params/LeaveDate/text() ') AS NVARCHAR(MAX)) AS Datetime)
SELECT @ReturnDate = CAST(CAST(@Message.query( '/Params/ReturnDate/text() ') AS NVARCHAR(MAX)) AS Datetime)
SELECT @Flightid = CAST(CAST(@Message.query( '/Params/Flightid/text() ') AS NVARCHAR(MAX)) AS int)
SELECT @clickDate = CAST(CAST(@Message.query( '/Params/clickDate/text() ') AS NVARCHAR(MAX)) AS Datetime)
SELECT @IpAddress = CAST(CAST(@Message.query( '/Params/IpAddress/text() ') AS NVARCHAR(MAX)) AS varchar(50))
INSERT INTO Clicklog(Departure ,Destination ,LeaveDate ,ReturnDate,Flightid ,clickDate ,IpAddress)
values(@Departure ,@Destination ,@LeaveDate ,@ReturnDate,@Flightid ,@clickDate,@IpAddress);
END
--select * from clicklog
END
GO
------------
-------------
CREATE PROCEDURE sendclicklog
(
@Departure char(3),
@Destination char(3),
@LeaveDate datetime,
@ReturnDate datetime,
@Flightid int,
@clickDate datetime ,
@IpAddress varchar(50)
)
AS
BEGIN
DECLARE @MessageBody XML
CREATE TABLE #ProcParams (
Departure char(3),
Destination char(3),
LeaveDate datetime,
ReturnDate datetime,
Flightid int,
clickDate datetime ,
IpAddress varchar(50)
)
INSERT INTO #ProcParams(Departure ,Destination ,LeaveDate ,ReturnDate,Flightid ,clickDate ,IpAddress)
VALUES(@Departure ,@Destination ,@LeaveDate ,@ReturnDate,@Flightid ,@clickDate,@IpAddress)
SELECT @MessageBody = (SELECT * FROM #ProcParams FOR XML PATH ( 'Params '), TYPE);
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [RecordClickService]
TO SERVICE 'ClickService '
ON CONTRACT [ClickContract]
WITH ENCRYPTION = OFF, LIFETIME = 600;
SEND ON CONVERSATION @Handle MESSAGE TYPE [RecordClick](@MessageBody);
END
GO
ALTER QUEUE [ClickQueue] WITH ACTIVATION
(
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = clicklogadditem,
EXECUTE AS OWNER
);
GO
------------------------------------
EXECUTE sendclicklog 'd ', 'g ', '1/9/2005 ', '1/9/2005 ',30, '1/9/2005 ', '1110 '
select * from clicklog
select * from clickqueue
------解决方案--------------------
新建一个数据库是90的
而附加的数据库或者是还原的数据库却是80的
以上脚本只能在90以上版本执行，建议将数据库升级到90
[解决办法]
有可能是数据库的 Service Broker 功能禁用了, 楼主试试执行下面的语句把选项开启
ALTER DATABASE 库名
SET ENABLE_BROKER
