SQL Adapter: Unexpected data conversions

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › SQL Adapter: Unexpected data conversions

Viewing 1 reply thread
  • Author
    Posts
    • July 17, 2008 at 4:18 PM #20182
      community-content

        I have a series of Stored Procedures in a database that I am calling via the SQL Adapter.  If/When I pass in my parms in certain scenarios, I’m seeing slightly different results that I’d like.  Here are some examples:

        NULLs
        When I execute a map from a source xml to the destination xml that is passed to the adapter, the empty string values are obviously not NULL as far as SQL Server is concerned, but if they are date fields or integers, they default to the SP’s as ‘1/1/1900’ and 0, respectively.  I’d prefer these to remain as NULL is I can help it based on our customer needs.  Any ideas other than adding the custom conversions in the SP’s?  Just curious.

        Money
        If I pass in, let’s say Amount=”50.00″ from a source xml and this is mapped to my SP schema, SQL Server complains about an invalid conversion from varchar to int.  My SP schema has ‘decimal’ as the data type and the SQL Table has ‘money’ as its datatype.

        I like using the adapter as it makes development times a bit easier to cope with, but I’m not sure this out-weights the possible requirement to still add more code to my procedures.

        Thanks in advance!
        -Rich

      • July 17, 2008 at 8:43 PM #20184
        community-content

          When you map into the SQL message you need to omit the attributes/elements that you wish to be null

          i.e.
          Assuming a table with 9 columns string1,2,3 date1,2,3 and int1,2,3 and this Xml
          <record string1=”data” string3=”” date1=”2008-07-18″ date3=”” int1=”1″ int3=””>

          the resulting row will be

          data, null, ”, 18/7/2008, null, 1/1/1900, 1, null, 0 

          • July 18, 2008 at 7:25 AM #20188
            community-content

              HiI Greg…thanks for the response.

              My main challenge is that *most* times, the source document will have these values, including valid dates and integers.  My map has all points covered as far as providing a link from the source doc to the destination doc.  So, I guess I’m looking for a way to make this a little more “dynamic” as in, if there is a value, provide it..otherwise, don’t include the parm and I was hoping I could do this without to many more changes.

              Thoughts?
              -Rich

        • Author
          Posts
        Viewing 1 reply thread
        • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.