The Code-Bin
Links
Home
Add your code!
All Listings
About
Latest Entry
Featured Scripts
Author's Website
Latest Entries
FFMPEG Thumbnail Scr...
PHP, 0.8KB
Jul. 29, 10:24pm
John
Z80 Assembler, 190 bytes
Feb. 17, 3:36am
John
Z80 Assembler, 176 bytes
Sep. 13, 2:19am
John
Z80 Assembler, 77 bytes
Sep. 13, 2:18am
John
Z80 Assembler, 209 bytes
Sep. 13, 2:17am
Helper stored procedures for service broker
Posted by: Jeroen Mostert | July 7, 2010 @ 2:06pm
T-SQL Code
[
Download
]
CREATE FUNCTION [dbo].[sb_SelectInitiatorConversationHandle]( @InitiatorServiceName SYSNAME, @TargetServiceName SYSNAME ) RETURNS UNIQUEIDENTIFIER AS BEGIN DECLARE @ConversationHandle UNIQUEIDENTIFIER; SELECT TOP(1) @ConversationHandle = [conversation_handle] FROM sys.conversation_endpoints WITH (NOLOCK) WHERE far_service = @TargetServiceName AND service_id = ( SELECT service_id FROM sys.services WITH (NOLOCK) WHERE name = @InitiatorServiceName ) AND is_initiator = 1; RETURN @ConversationHandle; END; GO CREATE FUNCTION [dbo].[sb_SelectTargetConversationHandle]( @InitiatorServiceName SYSNAME, @TargetServiceName SYSNAME ) RETURNS UNIQUEIDENTIFIER AS BEGIN RETURN ( SELECT conversation_handle FROM sys.conversation_endpoints WITH (NOLOCK) WHERE conversation_id = ( SELECT TOP(1) conversation_id FROM sys.conversation_endpoints WITH (NOLOCK) WHERE far_service = @TargetServiceName AND service_id = ( SELECT service_id FROM sys.services WITH (NOLOCK) WHERE name = @InitiatorServiceName ) AND is_initiator = 1 ) AND is_initiator = 0 ); END; GO ALTER PROCEDURE [dbo].[sb_InitializeConversation](@InitiatorServiceName SYSNAME, @TargetServiceName SYSNAME, @InitiatorConversationHandle UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000' OUTPUT, @TargetConversationHandle UNIQUEIDENTIFIER = '00000000-0000-0000-0000-000000000000' OUTPUT) AS BEGIN SET NOCOUNT ON; DECLARE @message NVARCHAR(MAX); IF (SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID()) = 0 BEGIN SELECT @message = 'Service broker is disabled for database ''' + DB_NAME() + '''. Use "ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET ENABLE_BROKER" to enable it.'; RAISERROR(@message, 11, 1); RETURN -1; END; IF NOT EXISTS (SELECT * FROM sys.services WHERE name = @InitiatorServiceName) BEGIN SELECT @message = 'Initiator service ''' + @InitiatorServiceName + ''' does not exist on the local server. Service names are case- and accent-sensitive.'; RAISERROR(@message, 11, 1); RETURN -1; END; IF NOT EXISTS (SELECT * FROM sys.services WHERE name = @TargetServiceName) BEGIN SELECT @message = 'Target service ''' + @TargetServiceName + ''' does not exist on the local server. Service names are case- and accent-sensitive. This procedure cannot be used to create conversations to remote services.'; RAISERROR(@message, 11, 1); RETURN -1; END; DECLARE @queueName SYSNAME; SELECT @queueName = dbo.sb_SelectServiceQueue(@TargetServiceName); DECLARE @xml NVARCHAR(MAX); DECLARE @parameters NVARCHAR(MAX); SELECT @xml = 'BEGIN TRY' + CHAR(13) + CHAR(10) + ' BEGIN' + CHAR(13) + CHAR(10) + ' BEGIN DIALOG CONVERSATION @initiatorConversationHandle FROM SERVICE ' + @InitiatorServiceName + ' TO SERVICE ''' + @TargetServiceName + ''' WITH ENCRYPTION = OFF; ' + CHAR(13) + CHAR(10) + ' SEND ON CONVERSATION @initiatorConversationHandle(''''); ' + CHAR(13) + CHAR(10) + ' DECLARE @dummy TABLE([message] VARBINARY(MAX)); ' + CHAR(13) + CHAR(10) + ' SELECT @targetConversationHandle = conversation_handle FROM sys.conversation_endpoints WHERE conversation_id = (SELECT conversation_id FROM sys.conversation_endpoints WHERE conversation_handle = @initiatorConversationHandle) AND is_initiator = 0;' + CHAR(13) + CHAR(10) + ' IF @@ROWCOUNT = 0 RAISERROR(''Send from ''''' + @InitiatorServiceName + ''''' to ''''' + @TargetServiceName + ''''' failed. Contract DEFAULT not mapped?'', 11, 0);' + CHAR(13) + CHAR(10) + ' RECEIVE message_body FROM ' + @queueName + ' INTO @dummy WHERE conversation_handle = @targetConversationHandle;' + CHAR(13) + CHAR(10) + ' IF @@ROWCOUNT = 0 RAISERROR(''Receive from ''''' + @InitiatorServiceName + ''''' to ''''' + @TargetServiceName + ''''' failed.'', 11, 0);' + CHAR(13) + CHAR(10) + ' END' + CHAR(13) + CHAR(10) + 'END TRY' + CHAR(13) + CHAR(10) + 'BEGIN CATCH' + CHAR(13) + CHAR(10) + ' BEGIN' + CHAR(13) + CHAR(10) + ' DECLARE @message NVARCHAR(MAX) = ERROR_MESSAGE(); ' + CHAR(13) + CHAR(10) + ' IF @initiatorConversationHandle IS NOT NULL ' + CHAR(13) + CHAR(10) + ' END CONVERSATION @initiatorConversationHandle WITH CLEANUP;' + CHAR(13) + CHAR(10) + ' SET @initiatorConversationHandle = NULL;' + CHAR(13) + CHAR(10) + ' SET @targetConversationHandle = NULL;' + CHAR(13) + CHAR(10) + ' RAISERROR(@message, 11, 0);' + CHAR(13) + CHAR(10) + ' END' + CHAR(13) + CHAR(10) + 'END CATCH'; ; SELECT @parameters = N'@initiatorConversationHandle UNIQUEIDENTIFIER OUTPUT, @targetConversationHandle UNIQUEIDENTIFIER OUTPUT'; --PRINT @xml; DECLARE @ReturnTable INT; SELECT @ReturnTable = CASE WHEN NOT (@InitiatorConversationHandle IS NULL AND @TargetConversationHandle IS NULL) THEN 1 ELSE 0 END; DECLARE @result INT; EXEC @result = sp_executesql @xml, @parameters, @initiatorConversationHandle = @initiatorConversationHandle OUTPUT, @targetConversationHandle = @targetConversationHandle OUTPUT; IF @result <> 0 OR @InitiatorConversationHandle IS NULL OR @TargetConversationHandle IS NULL RETURN -1; IF @ReturnTable = 1 SELECT @initiatorConversationHandle AS InitiatorConversationHandle, @targetConversationHandle AS TargetConversationHandle; END;
Syntax Highlighting
[
Open in new window
]
Author Comments
Using this effectively requires VIEW DEFINITION permissions, but I'm not sure on what objects. GRANT VIEW ANY DEFINITION TO PUBLIC will do the trick if you're not too anal about security.
Rating
4.73 / 8
41 Votes