none
BizTalk 2010 passing string to stored procedure even when column defined as Int

    Question

  • I have a BizTalk 2010 application that passes data to a stored procedure to insert into an MSSQL table, based on an XML file that is read from a directory.  The file is read fine and data is brought in, but any field that is blank appears to be passed to the stored procedure as an empty STRING, even though it is defined as an int in the BizTalk Schema.  So I get the following error

    The adapter "SQL" raised an error maessage.  Details "HRESULT=0x80040e07" Description="Error converting data type nvarchar to numeric."

    I have set the numeric items to have default values in the incoming and outgoing schema with no change.  If I populate all the numeric values in the incoming file (with 0) it works, but they will not always have values so that is not a viable solution.

    Thanks

    Karl

    Wednesday, February 22, 2012 7:39 PM

Answers

  • Hi Karl

    Other option can be custom pipline which just stripes of all the empty elements from outgoing message (but dont forget to assign default value to param in stored proc). Using functoid is a good option for small schema but in case of large schema, it can be very time consuming.

    To design a send piepline, use XSL Transform component (in encode) which resides in Biztalk installation folder (C:\Program Files (x86)\Microsoft BizTalk Server 2010\SDK\Samples\Pipelines\XslTransformComponent). 

    Create xsl file and use this code 

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="@*|node()">
      <xsl:if test=". != ''">
        <xsl:copy>
          <xsl:apply-templates select="@*|node()">
        </xsl:apply-templates></xsl:copy>
      </xsl:if>
    </xsl:template>
    </xsl:stylesheet>

    I used above pipeline to stripe off empty user defined table type tag from schema because sql doesnt allow to assign default null value to them in stored proc.

    Let me know if you have any ques.

    Regards

    Bharat


    • Edited by Bharat Bhatia Thursday, February 23, 2012 1:57 PM
    • Marked as answer by khalbert Thursday, February 23, 2012 11:01 PM
    Wednesday, February 22, 2012 10:08 PM
  • Bharat,

    Yes that would work.  I have decided to take a different path and modify the SP in the database to convert the incoming values to numeric becase it seems that Biztalk is passing varchar no matter what and I have to define the incoming fields as varchar else the error occurs, so changing before the insert into the table appears to be the best method.

    Thanks for your response

    Karl

    • Marked as answer by khalbert Thursday, February 23, 2012 11:01 PM
    Thursday, February 23, 2012 11:01 PM

All replies

  • Hi Karl,

    IMO you should use a BizTalk MAP to create the input message to your stored procedure. You would need to use Value mapping functoid(for conditional mapping of output element) to check if the value is blank and assign it to 0(numeric).

    Please see here  and here(more relevant) about value mapping functoid and conditional mapping.

    HTH,Thanks, Naushad (MCC/MCTS) http://alamnaushad.wordpress.com |@naushadalam

    If this is helpful or answers your question - please mark accordingly! Please "Vote As Helpful" if this was useful while resolving your question!


    Wednesday, February 22, 2012 7:43 PM
    Moderator
  • Naushad,

    Thanks for your response, I am using a map between the incoming schema and outgoing schema.  I will look into using a functoid to set values to those numeric items that come in with no value.

    Thanks

    Wednesday, February 22, 2012 8:08 PM
  • Hi Karl

    Other option can be custom pipline which just stripes of all the empty elements from outgoing message (but dont forget to assign default value to param in stored proc). Using functoid is a good option for small schema but in case of large schema, it can be very time consuming.

    To design a send piepline, use XSL Transform component (in encode) which resides in Biztalk installation folder (C:\Program Files (x86)\Microsoft BizTalk Server 2010\SDK\Samples\Pipelines\XslTransformComponent). 

    Create xsl file and use this code 

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:template match="@*|node()">
      <xsl:if test=". != ''">
        <xsl:copy>
          <xsl:apply-templates select="@*|node()">
        </xsl:apply-templates></xsl:copy>
      </xsl:if>
    </xsl:template>
    </xsl:stylesheet>

    I used above pipeline to stripe off empty user defined table type tag from schema because sql doesnt allow to assign default null value to them in stored proc.

    Let me know if you have any ques.

    Regards

    Bharat


    • Edited by Bharat Bhatia Thursday, February 23, 2012 1:57 PM
    • Marked as answer by khalbert Thursday, February 23, 2012 11:01 PM
    Wednesday, February 22, 2012 10:08 PM
  • Bharat,

    Yes that would work.  I have decided to take a different path and modify the SP in the database to convert the incoming values to numeric becase it seems that Biztalk is passing varchar no matter what and I have to define the incoming fields as varchar else the error occurs, so changing before the insert into the table appears to be the best method.

    Thanks for your response

    Karl

    • Marked as answer by khalbert Thursday, February 23, 2012 11:01 PM
    Thursday, February 23, 2012 11:01 PM