Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

How to pass a NULL value in a message to a queue in SQL Server

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
22 Jul 2011Apache 14.3K   1  
How to pass a NULL value in a message to a queue in SQL Server

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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0


Written By
bugcollect.com
United States United States
Remus Rusanu is a developer with the SQL Server team.

Comments and Discussions

 
-- There are no messages in this forum --