none
Problems with WCF SQL Server Adapter and SQL Type decimal with huge precision RRS feed

  • Question

  • I have the following problem with the WCF SQL Server adapter and the SQL type decimal
    I have generated schemas for insert from a table which includes a decimal (38,20)
    When trying to insert values like 4123423.34 the result in the column is 412342334.000000
    It is as if the point is ignored.
    If i change the column to decimal (18,4) there is no problem.

    Windows Server 2003 sp2 for both biztalk and sql server
    SQL Server 2005 sp2
    BizTalk 2006 R2
    Friday, January 15, 2010 10:57 AM

Answers

  • Solution:

    When precision gets above 28 for decimal xml type string is used.

    This makes the WCF SQL adapter attempting to cast the supplied value and in doing so applies regional settings for the user running the send/receive adapter more precisely the decimal separator, which in our case was ,

    So we mapped the . to an , and the problem was solved and the other way around a SELECT REPLACE (..., '.', ',') was needed.

    Tuesday, April 20, 2010 8:51 AM

All replies

  • Hi,

    -"When trying to insert values like 4123423.34 the result in the column is 412342334.000000"
    Could you show us the step list you setup the WCF adapter?
    -"If i change the column to decimal (18,4) there is no problem."
    how about other formaters, say, decimal(18,5).

    Regards
    This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support
    Tuesday, January 19, 2010 10:32 AM
    Moderator
  • First i install the WCF LOB Adapter SDK sp2
    Then i install the Microsoft BizTalk Adapter for SQL Server
    Then i set up the SQL adapter inside BizTalk Server Administration to run under BizTalkServerAdministration
    The schemas are made with Add -> Add Generated Items... -> Add Adapter Metadata -> WCF-SQL and then choosing the right table with select, insert, update, delete
    The send port is using the following binding:

        <SendPort Name="sendportname" IsStatic="true" IsTwoWay="true" BindingOption="1">
          <Description xsi:nil="true" />
          <TransmitPipeline Name="Microsoft.BizTalk.DefaultPipelines.XMLTransmit" FullyQualifiedName="Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Type="2" TrackingOption="ServiceStartEnd MessageSendReceive PipelineEvents" Description="" />
          <PrimaryTransport>
            <Address>mssql://server/instance/database?</Address>
            <TransportType Name="WCF SQL Server Adapter" Capabilities="779" ConfigurationClsid="59b35d03-6a06-4734-a249-ef561254ecf7" />
            <TransportTypeData>&lt;CustomProps&gt;&lt;InboundBodyPathExpression vt="8" /&gt;&lt;InboundBodyLocation vt="8"&gt;UseBodyElement&lt;/InboundBodyLocation&gt;&lt;UseSSO vt="11"&gt;0&lt;/UseSSO&gt;&lt;BindingConfiguration vt="8"&gt;&amp;lt;binding name="sqlBinding" useAmbientTransaction="true" /&amp;gt;&lt;/BindingConfiguration&gt;&lt;OutboundXmlTemplate vt="8"&gt;&amp;lt;bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="xml"/&amp;gt;&lt;/OutboundXmlTemplate&gt;&lt;Identity vt="8" /&gt;&lt;Password vt="8"&gt;password&lt;/Password&gt;&lt;ProxyUserName vt="8" /&gt;&lt;AffiliateApplicationName vt="8" /&gt;&lt;StaticAction vt="8"&gt;&amp;lt;BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&amp;gt;
      &amp;lt;Operation Name="Insert" Action="TableOp/Insert/dbo/tablename" /&amp;gt;
      &amp;lt;Operation Name="Select" Action="TableOp/Select/dbo/tablename" /&amp;gt;
      &amp;lt;Operation Name="Delete" Action="TableOp/Delete/dbo/tablename" /&amp;gt;
      &amp;lt;Operation Name="Update" Action="TableOp/Update/dbo/tablename" /&amp;gt;
    &amp;lt;/BtsActionMapping&amp;gt;&lt;/StaticAction&gt;&lt;BindingType vt="8"&gt;sqlBinding&lt;/BindingType&gt;&lt;UserName vt="8"&gt;username&lt;/UserName&gt;&lt;InboundNodeEncoding vt="8"&gt;Xml&lt;/InboundNodeEncoding&gt;&lt;PropagateFaultMessage vt="11"&gt;-1&lt;/PropagateFaultMessage&gt;&lt;ProxyAddress vt="8" /&gt;&lt;EndpointBehaviorConfiguration vt="8"&gt;&amp;lt;behavior name="EndpointBehavior" /&amp;gt;&lt;/EndpointBehaviorConfiguration&gt;&lt;OutboundBodyLocation vt="8"&gt;UseBodyElement&lt;/OutboundBodyLocation&gt;&lt;/CustomProps&gt;</TransportTypeData>
            <RetryCount>0</RetryCount>
            <RetryInterval>0</RetryInterval>
            <ServiceWindowEnabled>false</ServiceWindowEnabled>
            <FromTime>2000-01-01T22:00:00</FromTime>
            <ToTime>2000-01-01T21:59:59</ToTime>
            <Primary>true</Primary>
            <OrderedDelivery>false</OrderedDelivery>
            <DeliveryNotification>1</DeliveryNotification>
            <SendHandler Name="BizTalkServerApplication" HostTrusted="true">
              <TransportType Name="WCF SQL Server Adapter" Capabilities="779" ConfigurationClsid="59b35d03-6a06-4734-a249-ef561254ecf7" />
            </SendHandler>
          </PrimaryTransport>
          <SecondaryTransport>
            <Address />
            <RetryCount>3</RetryCount>
            <RetryInterval>5</RetryInterval>
            <ServiceWindowEnabled>false</ServiceWindowEnabled>
            <FromTime>2000-01-01T22:00:00</FromTime>
            <ToTime>2000-01-01T21:59:59</ToTime>
            <Primary>false</Primary>
            <OrderedDelivery>false</OrderedDelivery>
            <DeliveryNotification>1</DeliveryNotification>
            <SendHandler xsi:nil="true" />
          </SecondaryTransport>
          <ReceivePipeline Name="Microsoft.BizTalk.DefaultPipelines.XMLReceive" FullyQualifiedName="Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Type="1" TrackingOption="ServiceStartEnd MessageSendReceive PipelineEvents" Description="" />
          <ReceivePipelineData xsi:nil="true" />
          <Tracking>0</Tracking>
          <Filter />
          <Transforms />
          <InboundTransforms />
          <OrderedDelivery>false</OrderedDelivery>
          <Priority>5</Priority>
          <StopSendingOnFailure>false</StopSendingOnFailure>
          <RouteFailedMessage>false</RouteFailedMessage>
          <ApplicationName>applicationname</ApplicationName>
        </SendPort>

    I have not yet tried experimenting with different precissions for the table, i will do that in the near future.
    Tuesday, January 19, 2010 2:47 PM
  • I have now done some experiments:

    If precision >= 29 then any decimal points are ignored and the number is read as such, eq. 12345.123 becomes 12345123 in the table
    If precision <= 28 then the number is read with decimal point, eq. 12345.123 becomes 12345.123 in the table

    This is independent of both the number to be stored and of decimal scale which can have any value allowed by SQL Server.
    Monday, January 25, 2010 2:40 PM
  • I checked for a known issue on this and could not find one. The WCF OracleBinding has many limitations on data types and what can or cannot be exposed using WCF so I guess it is not surprising there are some for the WCF-SQL adapter. This is probably a bug in the adapter. You could report it using MS Support - they will usually not charge you if it is their issue, and they would then issue a hotfix.

    Probably more important than the binding details is the schema generated for the SQL object. Could you check what datatype and precision are in the generated schema for the column with >= 29 precision?

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, January 27, 2010 5:39 AM
    Moderator
  • It generates the following type for a Decimal(38,20):

    <element minOccurs="0" maxOccurs="1" name="ColumnName" nillable="true">
     <simpleType>
      <restriction base="string">
       <maxLength value="40" />
      </restriction>
     </simpleType>
    </element>

    The data type is a string as described in
    http://msdn.microsoft.com/en-us/library/dd788541(BTS.10).aspx

    If someone could confirm this behaviour i would be more than grateful
    Wednesday, January 27, 2010 12:58 PM
  • I created a column with type Decimal(38,20) and the schema generated matches exactly what you pasted. 

    I then tried doing a table operation select on it and got this error:

    The adapter failed to transmit message going to send port "GetLargePrecisionValue" with URL "mssql://benc-newbase//LocalTesting?". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.OverflowException: Conversion overflows.
       at System.Data.SqlClient.SqlBuffer.get_Decimal()
       at System.Data.SqlClient.SqlBuffer.get_Value()
       at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
       at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
       at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
       at Microsoft.Adapters.Sql.MetadataHelper.WriteDotNetObjectToXmlDictionaryWriter(XmlDictionaryWriter writer, SqlDataReader dataReader, Int32 fieldIndex, Int32 length, SqlDbType sqlDbType)
       at Microsoft.Adapters.Sql.SelectBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
       at Microsoft.Adapters.AdapterUtilities.AdapterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
       at System.ServiceModel.Channels.Message.WriteBodyContents(XmlDictionaryWriter writer)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessageStream(Message wcfMessage, IAdapterConfigInboundMessageMarshalling config)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessage(IBaseMessageFactory messageFactory, IAdapterConfigInboundMessageMarshalling marshallingConfig, Message wcfMessage)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".

    I am thinking that working with a number with this precision must not work at all with the WCF-SQL adapter.

    I can submit this issue through an MVP site, but it could take a couple weeks before it is triaged and addressed. If you need a production hotfix sooner, please contact MS Support directly.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Wednesday, January 27, 2010 5:00 PM
    Moderator
  • I get the same error depending on the size of the number i try to select.
    The limit is somewhere around 800,000,000 (800 millions)

    I will try to contact MS Support.
    Thursday, January 28, 2010 2:17 PM
  • Solution:

    When precision gets above 28 for decimal xml type string is used.

    This makes the WCF SQL adapter attempting to cast the supplied value and in doing so applies regional settings for the user running the send/receive adapter more precisely the decimal separator, which in our case was ,

    So we mapped the . to an , and the problem was solved and the other way around a SELECT REPLACE (..., '.', ',') was needed.

    Tuesday, April 20, 2010 8:51 AM