随便新建一个数据库,执行以下脚本,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
ENDGO
------------
-------------
CREATE PROCEDURE sendclicklog(
@Departure char(3),
@Destination char(3),
@LeaveDate datetime,
@ReturnDate datetime,
@Flightid int,
@clickDate datetime ,
@IpAddress varchar(50)
)ASBEGIN
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
但如果附加的数据库或者是还原的数据库,执行以下脚本,连队列消息都发送不了。会是什么原因?
------------------------------
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
ENDGO
------------
-------------
CREATE PROCEDURE sendclicklog(
@Departure char(3),
@Destination char(3),
@LeaveDate datetime,
@ReturnDate datetime,
@Flightid int,
@clickDate datetime ,
@IpAddress varchar(50)
)ASBEGIN
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
解决方案 »
- 如何将数据中的错误提示在delphi界面中显示
- window2000 下远程连接数据库的问题!
- 如何判断表中的某个字段是否存在?
- 求教:update HD_Phy_Exch set EA9=(EA8/EA5)*100 where ExchAID=10 怎么更新不了EA9呢?
- 关于触发器的
- 存储过程,把三个不同表里的数据merge到一个新建的表中
- help + sos + 119+110 帮俺看看一个雷人的需求。。。
- sql执行insert操作,出错,插入的字段数量有最大限制吗?具体见内:
- 求助:SQL server乱码问题,急
- 到中华英才网应聘的问题
- 无法远程连接SQLSERVER2000,怎么回事?
- 求一个模糊查询的SQL
SET ENABLE_BROKER
无法启用数据库 "xxx" 中的 Service Broker,因为数据库(C5025E10-12CB-4BA2-9823-277ED551DD7A)中的 Service Broker GUID 与 sys.databases (ACEFA90E-09E6-4BB1-9A3F-514F5AFF8093)中的不匹配。则可用下面的语句, 指定数据库应接收新的 Broker 标识符
ALTER DATABASE tempdb
SET NEW_BROKER
SET ENABLE_BROKER这句我好像试过,不行。我新建了一个数据库,再把数据导过去,就可以了...