Sending to ServiceBus with BrokerProperties fails with Bad Request #9
Description
When I run the following SQL script to send a TestMessage message to Azure Service Bus topic {ServiceBusTopicName}:
declare @headers nvarchar(max), @url nvarchar(max), @payload nvarchar(max), @response nvarchar(max);
set @headers = json_object(
'Accept': 'application/xml',
'Content-Type': 'application/json',
'BrokerProperties': '{"Label": "TestMessage", "SessionId": "Session1"}'
);
set @payload = json_object('MessageId': 1);
select @headers, @payload;
set @url = 'https://{ServiceBusName}.servicebus.windows.net/{ServiceBusTopicName}/messages'
exec sp_invoke_external_rest_endpoint
@url = @url,
@method = 'POST',
@headers = @headers,
@payload = @payload,
@credential = [https://{ServiceBusName}.servicebus.windows.net],
@response = @response output
select cast(@response as xml)
goIt fails with 400 Bad Request pointing to 'BrokerProperties' is invalid:
<output>
<response>
<status>
<http code="400" description="Bad Request" />
</status>
<headers>
<header key="Date" value="Thu, 05 Oct 2023 13:32:20 GMT" />
<header key="Transfer-Encoding" value="chunked" />
<header key="Content-Type" value="application/xml; charset=utf-8" />
<header key="Server" value="Microsoft-HTTPAPI/2.0" />
<header key="Strict-Transport-Security" value="max-age=31536000" />
</headers>
</response>
<result>
<Error>
<Code>400</Code>
<Detail>The value '{\"Label\": \"TestMessage\", \"SessionId\": \"Session1\"}' of the HTTP header 'BrokerProperties' is invalid. TrackingId:1030e3ab-5a69-4414-b28e-73af5a76888d_G58, SystemTracker:***, Timestamp:2023-10-05T13:32:19</Detail>
</Error>
</result>
</output>However, once I remove BrokerProperties from the header, it works returning 201 Created:
<output>
<response>
<status>
<http code="201" description="Created" />
</status>
<headers>
<header key="Date" value="Thu, 05 Oct 2023 13:39:17 GMT" />
<header key="Transfer-Encoding" value="chunked" />
<header key="Content-Type" value="application/xml; charset=utf-8" />
<header key="Server" value="Microsoft-HTTPAPI/2.0" />
<header key="Strict-Transport-Security" value="max-age=31536000" />
</headers>
</response>
</output>My best guess is sp_invoke_external_rest_endpoint does not correctly decode a value that contains encoded JSON
{"Accept":"application\/xml","Content-Type":"application\/json","BrokerProperties":"{\"Label\": \"TestMessage\", \"SessionId\": \"Session1\"}"}What am I doing wrong?
Do you have any workarounds?
It there better place to file this bug?