Last active
August 16, 2020 02:42
-
-
Save Protiguous/2646c288245a626838ae75402384a3c1 to your computer and use it in GitHub Desktop.
Easy Create Objects for Service Broker (works in SQL Server 2019 Developer Edition)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Based from https://sqlperformance.com/2014/03/sql-performance/configuring-service-broker | |
USE [master] | |
GO | |
ALTER DATABASE [Test] SET ENABLE_BROKER WITH NO_WAIT | |
GO | |
USE [Test] | |
GO | |
-- Try to drop the services. | |
DROP SERVICE [//Test/OutgoingMessageService]; | |
GO | |
DROP SERVICE [//Test/IncomingMessageService]; | |
GO | |
-- Try to drop the queues. THIS WILL ERASE ANY MESSAGES in the QUEUE!! | |
DROP QUEUE [//Test/IncomingMessageQueue]; | |
GO | |
DROP QUEUE [//Test/OutgoingMessageQueue]; | |
GO | |
-- Try to drop the contract. | |
DROP CONTRACT [//Test/MessageContract]; | |
GO | |
-- Try to drop the message type. | |
DROP MESSAGE TYPE [//Test/Message]; | |
GO | |
-- ********************************************************************************** | |
-- Now create the required objects. | |
-- ********************************************************************************** | |
CREATE MESSAGE TYPE [//Test/Message] VALIDATION = NONE; | |
GO | |
-- https://docs.microsoft.com/en-us/sql/t-sql/statements/create-contract-transact-sql | |
CREATE CONTRACT [//Test/MessageContract] ([//Test/Message] SENT BY ANY); --also | |
GO | |
-- https://docs.microsoft.com/en-us/sql/t-sql/statements/create-queue-transact-sql | |
CREATE QUEUE [//Test/IncomingMessageQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY] | |
GO | |
CREATE QUEUE [//Test/OutgoingMessageQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = ON) ON [PRIMARY] | |
GO | |
CREATE SERVICE [//Test/IncomingMessageService] ON QUEUE [//Test/IncomingMessageQueue] ([//Test/MessageContract]) | |
GO | |
CREATE SERVICE [//Test/OutgoingMessageService] ON QUEUE [//Test/OutgoingMessageQueue] ([//Test/MessageContract]) | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment