none
WCF SQL adapter error message "unable to switch the encoding" RRS feed

  • Question

  • Hi,

    I am trying to follow the instructions in article "Executing Stored Procedures With a Single XML Parameter " from Microsoft Biztalk Adapter for SQL Server documentation. The article is not very descriptive, there are few things I assumed like deploying the schema for the stored proc generated through "Consume Adapter Service" and the XML pipelines for the Send and Receive ports.

    I am receiving the following error while running the sample.

    "Error details: System.Data.SqlClient.SqlException: XML parsing: line 1, character 38, unable to switch the encoding"

    I am using the following message instance to pass :

    "<ns0:ADD_LAST_EMP_XML_INFO xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
      <ns0:xml_info>Address XYZ</ns0:xml_info>
      </ns0:ADD_LAST_EMP_XML_INFO>"


    What am I doing wrong?
    Tuesday, May 19, 2009 2:37 PM

Answers

  • The MSDN topic you are refering to caters to the following scenario:

    You have a stored procedure that takes a single parameter of type XML i.e. it can only contain an XML value. You do not want the over head of creating a VS project, generate metadata, create orchestrations, etc. So, all you do is just create a send port in BizTalk Admin Console and configure it to pick the XML file that just contains the parameter value. Note that this XML file is not the XML file for the request message. This is the XML file for the parameter value that you need to pass.

    Now, you also somehow need to create a message for the stored procedure that conforms to the SP schema. This is what you do on the Message tab of the send port where you use a template to create the message.

    Coming back to the issue you are facing, I am assuming you are doing two contradicting things:

    1. You are passing the XML message that conforms to the request schema for the SP.
    2. Then, you are also using the template option in the Message tab to create the message (which you wouldn't need becuase of point 1 above)

    So, if you are using the template option just pass the XML value for the parameter and not the XML message for the SP.

    Another thing that I notice is that in your request message you are passing a string value for the xml_info field. Note that if you created the SP by running the scripts provided with the sample, then xml_info should only contain XML values.

    Try doing the following things to make your project work:

    1. Creat an XML file that will be passed for the xml_info parameter. Something like:
        <Address>
          <City>ABC</City>
          <Country>XYZ</XYZ>
        </Address>

    2. Copy this file to you file receive location

    3. In your send port, use the template option as described in the topic to use the envelope to form the request message

    Rgds/Nitin

    • Marked as answer by Puneet Jain Thursday, May 21, 2009 9:30 AM
    Wednesday, May 20, 2009 4:11 PM

All replies

  • Hi,

    What is  xml declaration of your xml message? Is it like this: <?xml version=”1.0″ encoding=”utf-8″ ?>? Try to change the xml declaration to:<?xml version=”1.0″ encoding=”utf-16″ ?>


    HTH, Randal van Splunteren - http://biztalkmessages.vansplunteren.net - Please mark as answered if this answers your question.
    Wednesday, May 20, 2009 7:29 AM
    Moderator
  • I have not set it, however I tried with <xml version=”1.0″ encoding=”utf-16″> as well, however it still fails.

    Wednesday, May 20, 2009 9:25 AM
  • Hi Puneet,

    Can you please clarify the version of BizTalk you are using and also the version of the SQL server you are targetting?

    Thanks,
    sandeep
    Wednesday, May 20, 2009 11:22 AM
  • I am using Biztalk Server 2009 and SQL Server 2008.
    Wednesday, May 20, 2009 12:46 PM
  • The MSDN topic you are refering to caters to the following scenario:

    You have a stored procedure that takes a single parameter of type XML i.e. it can only contain an XML value. You do not want the over head of creating a VS project, generate metadata, create orchestrations, etc. So, all you do is just create a send port in BizTalk Admin Console and configure it to pick the XML file that just contains the parameter value. Note that this XML file is not the XML file for the request message. This is the XML file for the parameter value that you need to pass.

    Now, you also somehow need to create a message for the stored procedure that conforms to the SP schema. This is what you do on the Message tab of the send port where you use a template to create the message.

    Coming back to the issue you are facing, I am assuming you are doing two contradicting things:

    1. You are passing the XML message that conforms to the request schema for the SP.
    2. Then, you are also using the template option in the Message tab to create the message (which you wouldn't need becuase of point 1 above)

    So, if you are using the template option just pass the XML value for the parameter and not the XML message for the SP.

    Another thing that I notice is that in your request message you are passing a string value for the xml_info field. Note that if you created the SP by running the scripts provided with the sample, then xml_info should only contain XML values.

    Try doing the following things to make your project work:

    1. Creat an XML file that will be passed for the xml_info parameter. Something like:
        <Address>
          <City>ABC</City>
          <Country>XYZ</XYZ>
        </Address>

    2. Copy this file to you file receive location

    3. In your send port, use the template option as described in the topic to use the envelope to form the request message

    Rgds/Nitin

    • Marked as answer by Puneet Jain Thursday, May 21, 2009 9:30 AM
    Wednesday, May 20, 2009 4:11 PM
  • Also, if you are following the topic to the T, you would not need to deploy any schema. Also, you don't need any XML pipelines;PassThru would work.
    Wednesday, May 20, 2009 4:53 PM
  • Thanks Nitin, You pointed me in the right direction and I am now able to get the sample working.

    Cheers.....
    Thursday, May 21, 2009 9:31 AM
  • Where can one find the scripts/project associated with this particular walkthrough?

    I can't seem to find the sample project to download neither here

    http://msdn.microsoft.com/en-US/library/dd788535(v=bts.10).aspx

    nor here http://msdn.microsoft.com/en-us/biztalk/dd573301

    Someone please point out.


    -Perennial Newbie-

    Friday, April 26, 2013 5:39 PM
  • Hi Puneet.

     

    Like the OP said, your post explained much more than that topic. Thank you.

    I followed everything from that topic, as well as your post, and in the end, I got this when I dropped the input file:

    The adapter failed to transmit message going to send port "ExecProcedure" with URL "mssql://OurDBServer//OurDB/".
    It will be retransmitted after the retry interval specified for this Send Port.
    Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException:
    The start element with name "IRequest" and namespace "" was unexpected.
    Please ensure that your input XML conforms to the schema for the operation.

     IRequest is the root node of an incoming Xml request which gets sent to the Stored procedure via an Xml parameter.

    I understand the message has something to with schema of the incoming xml, but I just can't see why. Why does it care what the start element is, as long as it is well formed xml?

    This is for BTS2009, SQL Server 2008, with all service packs.  Thank you.


    -Perennial Newbie-

    Friday, April 26, 2013 6:37 PM