I am having problems inserting values into SQL Server columns of type decimal(38, 20) from BizTalk 2013 using WCF-SQL adapter. I get InvalidCastException with message: "System.InvalidCastException: Specified cast is not valid"
If I test column type decimal(18,18) it works.
Seems like the WCF-SQL adapter does not handle decimal with very high precision. Question is what is the limitation? And, if there is a workaround?
When I generate XSD from database table information, decimal(38,20) turns into xs:string with length restriction of 40. Maybe this is a sign of that WCF-SQL adapter cannot handle such precision...? I have also tested to alter the XSD to be xs:decimal, but no difference.
Anyone?
ADDITION: Did not find any "good" way to handle this limitation.
Final setup is: XML => WCF-SQL adapter => Stored Procedure with table type parameter containing varchar(40) columns => CAST table variable columns to decimal(38,20) one-by-one => INSERT into destination table.
So, solution was to modify table type to accept varchar, and manually convert in stored procedure.
Would be happy if someone could explain the better solution!
Decimal precision is limited to the .NET framework type. See here.
Also described in the BizTalk documentation here. "Decimal if precision <= 28. String if precision > 28".
So your way of handling with strings is an option. Use the Round functoid in your map to the SQL schema if you don't really need more than 29 positions.
Another option you could consider is changing the regional settings for the BizTalk host user running the send port. The current setting/language of your decimal separator is a comma instead of a dot (or the other way around) and not matching the data type for SQL Server. For this option you have to keep the type as string in your schema and keep it decimal in your SQL Server table.