婚纱网站免费源码免备案做网站 可以盈利吗
目录
准备
创建Message，Contract，Queue和Service
创建调用存储过程
启用SQL Agent并创建Job执行存储过程
调用demo
常见故障排除
准备
判断你的数据库YourDatabaseName是否启用了Service Broker
SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';如果未启用，可以通过以下命令启用。
ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;创建Message，Contract，Queue和Service
然后按照以下步骤分别创建Message，Contract，Queue和Service。
--1. 创建Message，Contract，Queue和Service
CREATE MESSAGE TYPE [DBTestSync] VALIDATION = NONE;CREATE CONTRACT [DBTestmessages] ([DBTestSync] SENT BY ANY)CREATE QUEUE [dbo].[DBTestSyncQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON) ;CREATE SERVICE [DBTestSyncService] ON QUEUE [dbo].[DBTestSyncQueue] ;
创建调用存储过程
The dequeue stored procedure (Service Program) is core of service broker implementation.
-
Processes the messages in the Queue
-
Handles the known errors – Inserts back the message into the queue and reprocesses it.
-
Send email notifications for unhandled exceptions.
-
Inserts the unknown errors into the ServiceBrokerException table.
--2. 创建调用的存储过程
--DEQUEUE SP/ SERVICE PROGRAM - This code is responsible for picking the message from the queue and processing data
CREATE proc [dbo].[SP_TEST_SERVICE_BROKER_IN_SP]
as
beginset nocount on;DECLARE @Handle UNIQUEIDENTIFIER ;DECLARE @MessageType SYSNAME ;DECLARE @Message XMLDECLARE @dt DATEtime =GETDATE()DECLARE @ID INT DECLARE @Name VARCHAR(50)declare @spname varchar(500)declare @ERROR VARCHAR(500)SET XACT_ABORT ONBEGIN TRY--BEGIN TRANWAITFOR( RECEIVE TOP (1) @Handle = conversation_handle,@MessageType = message_type_name,@Message = message_body FROM dbo.[DBTestSyncQueue]),TIMEOUT 1000--[DBTestSyncQueue]就是上面创建的Queue--SELECT cast(@Message as xml)set @spname =CAST(CAST(@Message.query('/mydata/SPName/text()') AS NVARCHAR(MAX)) AS VARCHAR(500))IF @spname='SP_TEST_SERVICE_BROKER'BEGINDeclare @Id int,@Name intSET @Id = convert(int, CAST(CAST(@Message.query('/mydata/Id/text()') AS NVARCHAR(MAX)) AS VARCHAR(50)))SET @Name = convert(int, CAST(CAST(@Message.query('/mydata/Name/text()') AS NVARCHAR(MAX)) AS VARCHAR(50)))update [Users] set Name=@Name where Id=@IdEND--COMMIT TRANEND TRYBEGIN CATCH--ROLLBACKDECLARE @ErrorHandle UNIQUEIDENTIFIER;SET @ERROR =ERROR_MESSAGE()IF (ERROR_NUMBER() = 1205 OR ERROR_NUMBER() = 1222 OR ERROR_NUMBER()=18452)BEGINBEGIN DIALOG CONVERSATION @ErrorHandleFROM SERVICE DBTestSyncService TO SERVICE 'DBTestSyncService'ON CONTRACT DBTestmessages WITH ENCRYPTION = OFF;SEND ON CONVERSATION @ErrorHandle MESSAGE TYPE DBTestSync(@MESSAGE);ENDELSEBEGININSERT INTO dbo.ServiceBrokerExceptionVALUES (@Message,@ERROR ,@dt);declare @messagebody varchar(5000)=concat('<b>Error in processing Service Broker Queue</b><BR><b>SPName:</b>',isnull(@spname,'SP Cant be Determined'),'<BR> <b>ERROR:</b>',isnull(@ERROR, 'Error cant be Determined')) exec msdb.dbo.sp_send_dbmail @profile_name='DBAMail', @recipients= 'group-agency360@alterdomusgroup.onmicrosoft.com',@subject='Service Broker: Error in processing Service Broker Queue',@body=@messagebody, @body_format='HTML'ENDEND CATCH;SET XACT_ABORT OFF
ENDGO
启用SQL Agent并创建Job执行存储过程
Job需要创建两个Steps,两个Steps内容都是exec SP_TEST_SERVICE_BROKER_IN_SP。
设置Job的Steps：需要创建两个steps。
Step 1：
1. 在General里面输入Step1-SP_TEST_SERVICE_BROKER_IN_SP
2. Database选择YourDatabaseName
3. Advanced选择Go to the next step
Step 2：
1. 在General里面输入Step2-SP_TEST_SERVICE_BROKER_IN_S
2. Database选择YourDatabaseName
3. Advanced选择Go to step: Step1-SP_TEST_SERVICE_BROKER_IN_SP
设置Job的Schedules:
Occurs every day every 10 second(s) between 12:00:00 AM and 11:59:59 PM. Schedule will be used starting on 11/14/2024.
Name: ServiceBrokerJob--自己随意命名
Schedule type: Recurring
Frequence
Occurs: Daily
Recurs every: 1 days(s)
Daily frequence
Occures every: 10 seconds--根据自己需要设置
Startint at: 12:00:00 AM
Ending at: 11:59:59 PM
Duration:
Start date: 11/14/2024--默认是你创建的日期
调用demo
--3. 调用demo。你可以在你调用的地方这样子写
create proc SP_TEST
(@Id int,@Name nvarchar(25)
)
AS
BEGINSELECT * INTO #TmpTest --Forming a Message-- FROM ( SELECT @Id AS Id ,@Name AS Name ,'SP_TEST_SERVICE_BROKER' AS SPName )a DECLARE @XMLMESSAGE XML; SELECT @XMLMESSAGE = (SELECT * FROM #TmpTest FOR XML PATH ('mydata'), TYPE); ----Sending Message to the Queue---- DECLARE @Handle UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @Handle FROM SERVICE DBTestSyncService TO SERVICE 'DBTestSyncService' ON CONTRACT DBTestmessages WITH ENCRYPTION = OFF; SEND ON CONVERSATION @Handle MESSAGE TYPE DBTestSync(@XMLMESSAGE);
END
常见故障排除
如果Service Broker没有按照预期结果运行，可以查看SQL Server Service Broker故障排除_sqlserver禁用servicebroker-CSDN博客