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:
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.
发表评论
Hs2KLE It as not that I want to duplicate your web site, but I really like the style and design. Could you tell me which style are you using? Or was it custom made?
3BQgZk These are actually wonderful ideas in about blogging.
h4x4Hq Ridiculous story there. What happened after? Good luck!
kKYCUL Excellent web site. Plenty of useful information here. I am sending it to some friends ans also sharing in delicious. And of course, thanks for your sweat!
7Fe3Ov Really appreciate you sharing this blog article.Thanks Again. Want more.
Really appreciate you sharing this blog article.Really looking forward to read more. Keep writing.
Wow, great blog post.Really thank you! Will read on...
I think this is a real great blog post.Really looking forward to read more. Keep writing.
Great blog post.Really thank you! Much obliged.
Major thankies for the post.Thanks Again.
Major thanks for the blog.Much thanks again. Really Great.
I value the blog.Thanks Again. Awesome.
I really liked your blog article.Thanks Again. Really Great.
Thanks again for the blog post.Much thanks again. Great.
Looking forward to reading more. Great blog.Really looking forward to read more. Will read on...
Thanks so much for the blog.Really thank you! Keep writing.
I loved your article.Really looking forward to read more. Cool.
wow, awesome blog.Really thank you! Fantastic.
wow, awesome blog.Thanks Again. Much obliged.
A round of applause for your article post.Thanks Again. Will read on...
Muchos Gracias for your post.Really looking forward to read more. Really Great.
A big thank you for your post.Really thank you! Awesome.
Thanks again for the post.Much thanks again. Much obliged.
Really informative post.Thanks Again. Keep writing.
Really enjoyed this article.Thanks Again. Really Cool.
Looking forward to reading more. Great blog post.Thanks Again. Really Cool.
Im thankful for the blog post.Really thank you! Will read on...
I loved your blog article.Really looking forward to read more. Really Great.
I value the article.Thanks Again. Awesome.
Awesome article. Will read on...
This is one awesome blog.Much thanks again.
Major thanks for the article post. Really Great.
Thanks for sharing, this is a fantastic article post.Much thanks again. Will read on...
Muchos Gracias for your blog article.Much thanks again. Great.
Muchos Gracias for your blog post.
I really liked your blog post.Really looking forward to read more. Fantastic.
Hey, thanks for the blog post.Really looking forward to read more. Much obliged.
Thanks a lot for the blog post.Really thank you! Great.
Thank you for your blog. Fantastic.
Say, you got a nice blog article.Really looking forward to read more.
I cannot thank you enough for the post. Want more.
Im obliged for the blog article.Much thanks again. Want more.
Awesome post.Really thank you! Want more.
Enjoyed every bit of your post.Really looking forward to read more. Really Great.
Great, thanks for sharing this blog article.Thanks Again. Really Cool.
Looking forward to reading more. Great post.Really looking forward to read more. Will read on...
Im grateful for the post. Cool.
Thanks for sharing, this is a fantastic blog post.Much thanks again. Really Great.
This is one awesome blog.Thanks Again. Much obliged.
wow, awesome blog post.Really looking forward to read more. Much obliged.