SQL Server
SQL Server
Azure Service Bus - Delete Message
See more Azure Service Bus Examples
Deletes a message from a queue or subscription.For more information, see https://docs.microsoft.com/en-us/rest/api/servicebus/unlock-message
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- Make the initial connection.
-- A single REST object, once connected, can be used for many Azure Service Bus REST API calls.
-- The auto-reconnect indicates that if the already-established HTTPS connection is closed,
-- then it will be automatically re-established as needed.
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, '<yournamespace>.servicebus.windows.net', 443, 1, @bAutoReconnect
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
RETURN
END
-- Let's load a previously computed SAS token and use it.
-- See Azure Shared Access Signature for an example to genenerate an Azure SAS token.
DECLARE @sbToken int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbToken OUT
EXEC sp_OAMethod @sbToken, 'LoadFile', @success OUT, 'qa_data/tokens/serviceBusSas.txt', 'utf-8'
-- Tell the REST object to use the Azure Shared Access Signature for authorization.
EXEC sp_OAMethod @sbToken, 'Prepend', @success OUT, 'SharedAccessSignature '
EXEC sp_OAMethod @sbToken, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', @sTmp0
-- ----------------------------------------------------------------------------------------------
-- The code above this comment could be placed inside a function/subroutine within the application
-- because the connection does not need to be made for every request. Once the connection is made
-- the app may send many requests..
-- ----------------------------------------------------------------------------------------------
-- To delete a message, we'll need either the SequenceNumber or MessageId.
-- When the message was peek-locked, the SequenceNumber and MessageId of the locked message
-- was returned in the HTTP response header named "BrokerProperties, which contained
-- JSON such as the following:
--
-- {
-- "DeliveryCount": 1,
-- "EnqueuedSequenceNumber": 3000004,
-- "EnqueuedTimeUtc": "Wed, 07 Dec 2016 17:42:48 GMT",
-- "Label": "M3",
-- "LockToken": "6eeee205-e541-4cb6-95fb-4c81da5f6858",
-- "LockedUntilUtc": "Thu, 08 Dec 2016 00:57:32 GMT",
-- "MessageId": "e8028116d63c43f191577f2789b1cca7",
-- "SequenceNumber": 4,
-- "State": "Active",
-- "TimeToLive": 922337193600
-- }
--
--
-- The path for unlocking a message is constructed as follows, depending on
-- whether it is a message on a queue or subscription:
--
-- (for a queue)
-- /{queuePath}/messages/{messageId|sequenceNumber}/{lockToken}
--
-- (for a subscription)
-- /{topicPath}/subscriptions/{subscriptionName}/messages/{messageId|sequenceNumber}/{lockToken}
-- This example will build a path to delete a message contained in a subscription.
DECLARE @topicName nvarchar(4000)
SELECT @topicName = 'gilaMonster'
DECLARE @subscriptionName nvarchar(4000)
SELECT @subscriptionName = 'feedings'
DECLARE @messageId nvarchar(4000)
SELECT @messageId = 'e8028116d63c43f191577f2789b1cca7'
DECLARE @lockToken nvarchar(4000)
SELECT @lockToken = '6eeee205-e541-4cb6-95fb-4c81da5f6858'
-- Build the path..
DECLARE @sbPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/{topicPath}/subscriptions/{subscriptionName}/messages/{messageId|sequenceNumber}/{lockToken}'
DECLARE @numReplaced int
EXEC sp_OAMethod @sbPath, 'Replace', @numReplaced OUT, '{topicPath}', @topicName
EXEC sp_OAMethod @sbPath, 'Replace', @numReplaced OUT, '{subscriptionName}', @subscriptionName
EXEC sp_OAMethod @sbPath, 'Replace', @numReplaced OUT, '{messageId|sequenceNumber}', @messageId
EXEC sp_OAMethod @sbPath, 'Replace', @numReplaced OUT, '{lockToken}', @lockToken
-- Add a timeout parameter.
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'timeout', '20'
-- Use the DELETE HTTP verb to delete the message.
DECLARE @sbResponseBody int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'DELETE', @sTmp0, @sbResponseBody
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbToken
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'Response Status Code = ' + @iTmp0
-- Check for a success response.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbToken
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbResponseBody
RETURN
END
-- If successful, the response body would be empty..
PRINT 'Success.'
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbToken
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @sbResponseBody
END
GO