The SEND Transact-SQL verb does not allow to send a NULL
message body, attempting to do so will result in error:
Msg 8433, Level 16, State 1, Line 11
The message body may not be NULL.
A zero-length UNICODE or binary string is allowed.
But there are ways to send a NULL
message body. One way is to completely omit the message body argument:
SEND ON CONVERSATION @handle MESSAGE TYPE [...];
Another way is to send a 0 length message body, which will be enqueued as a NULL
message body in the target queue:
SEND ON CONVERSATION @handle MESSAGE TYPE [...] (0x);
SEND ON CONVERSATION @handle MESSAGE TYPE [...] ('');
SEND ON CONVERSATION @handle MESSAGE TYPE [...] (N'');
All three forms above will enqueue the same message body: NULL
. This is true for both binary messages (VALIDATION = NONE
) and for XML messages (VALIDATION=WELL_FORMED_XML
).
Here is a short test script showing this:
create message type [BINARY] validation = none;
create message type [XML] validation = well_formed_xml;
go
create contract [TEST] (
[BINARY] sent by initiator,
[XML] sent by initiator);
go
create queue Sender;
create service Sender on queue Sender;
go
create queue Receiver;
create service Receiver on queue Receiver ([TEST]);
go
declare @h uniqueidentifier;
begin dialog conversation @h
from service [Sender]
to service N'Receiver', N'current database'
on contract [TEST]
with encryption = off;
send on conversation @h message type [BINARY];
send on conversation @h message type [BINARY] (0x);
send on conversation @h message type [XML];
send on conversation @h message type [XML] ('');
send on conversation @h message type [XML] (N'');
receive * from [Receiver];
go
The received message_body
column has a NULL
value for all 5 messages sent.