Wednesday, February 22, 2012 7:39 PM
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.
Wednesday, February 22, 2012 7:43 PMModerator
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).
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 8:08 PM
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.
Wednesday, February 22, 2012 10:08 PM
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.
Thursday, February 23, 2012 11:01 PM
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
- Marked As Answer by khalbert Thursday, February 23, 2012 11:01 PM