receive message_body cast as nvarchar(max) weirdness

Question

when i call this sql stmt from some jdbc code i'm getting a '?' prepended to the beginning of the message_body value!?

 

WAITFOR (RECEIVE Top (1) convert(varchar(255),conversation_handle),message_type_name,convert(nvarchar(max),message_body) FROM dbo.SomeQ), TIMEOUT 60000;

 

when i call it like this the problem goes away.

 

WAITFOR (RECEIVE Top (1) convert(varchar(255),conversation_handle),message_type_name,Case Validation WHEN 'X' THEN convert(nvarchar(max),CAST(message_body AS XML)) ELSE convert(nvarchar(max),CAST(N'<nothing/>' AS XML)) End FROM dbo.SomeQ), TIMEOUT 60000;

 

anyone have some insight into this situation!? 

 

BTW: the message type used here is defined as: WELL_FORMED_XML

 

thanks in advance

 

-mt

---

mike t.

Answer 1

Hi mike,

I would like to involve someone familiar with this issue to have a look. Thanks for your understanding.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi Mike,

Would it be possible to send us the scripts needed to repro the problem and a message?

Also could you send the snippet of java code you use to traverse the reply data ?

I've tried several variations of this , but wasn't able to reproduce what you are seeing.

  sql = "RECEIVE Top (1) convert(varchar(255)  ,conversation_handle) as 'conversation_handle',message_type_name,convert(nvarchar(max),message_body) as 'message_body' FROM dbo.RecvQueue";
    ...

 while(rs.next())

     {
                 //Retrieve by column name
             
                 String ch = rs.getString("conversation_handle");
                 String mb = rs.getString("message_body");
                 String mtn = rs.getString("message_type_name");

                 System.out.print("Conversation Handle: " + ch);
                 System.out.print(", Message Body: " + mb);
                 System.out.print(", Message Type Name: " + mtn);
                 System.out.printf("\n");

       }

 ...

debug:
Creating statement...
Conversation Handle: E43FAEBC-4D4C-E111-812C-000C29A92E83, Message Body:< Payload><Info>[Fire and Forget] Message 6</Info></Payload>, Message Type Name: DEFAULT

Thanks,

 

 - Greg

 

 

Answer 3

Mike,

i cam across something similar after upgrading my server to 2008. My service broker queue gets populated by an SSIS package, so my upgraded SSIS package was having trouble with implicit conversation between XML & VARCHAR(MAX) DataTypes. However, using the NVARCHAR(MAX) datatype with explicit conversion.

Just for sake of simplicity of code, i would recommend you try getting the CASE function out of the RECEIVE Block.

Within the Receive Block, store the message body into an local variable of XML data type. Once the recieve is successful, then try to perform the CASE login and conversion separately. Also note that the conversions and substitutions in the CASE Logic should have the same or compatible data types. I have a strong feeling that the problem here is not with the Service Broker piece, its simply with the Data Type conversion piece.

If you can provide me with the sample data where you face this issue, I could do some more troubleshooting. Hope this helps.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.