This blog describes an error that you might see if you are using the BizTalk WCF-SQL adapter to insert data into a SQL Always Database.
Use case
In the first place, our BizTalk Server inserts data into a custom database table on our BizTalk SQL Server. Moving this database to a new SQL Always On Server we got this error.
An ambient transaction is required for streaming data into a FILESTREAM column. Make sure you perform the operation with the UseAmbientTransaction binding proptery set to True.
Yes the property is spelt incorrectly in the error that is returned.
A previous blog tells you that you cannot use MSDTC with this type of SQL database and you must set useAmbientTransaction to false on the WCF-SQL adapter. That is what we did.
Diagnosis of UseAmbient Error
The WCF-SQL Adapter has these table operations configured.
The action with SetEDIMessage fails but the Update action does not. Where did this action come from? Reading the Microsoft documentation you find this.
That being the case, our table has a column “EDIMessage” of type nvarchar(MAX), the name of the operation is “SetEDIMessage”.
Mitigation
In view of this error two solutions spring to mind. Firstly revert to database that supports MSDTC and change useAmbientTransaction on the WCF-SQL adapter to true. This was not an option for us. Secondly change the SQL data type to nvarchar(4000), the maximum size allowed. The data in this column are EDIFACT messages. This is not a solution either because about 5% of out EDIFACT exceed the 4000 character limit.
Finally removing this update action from our interface closed out our problem. This was chosen for the simple reason that, there is not a good business reason to store this data. Indeed, we archive the EDIFACT message to the file system and we can retrieve it from there.
Conclusion
Given the points above, it is clear that if you use a SQL always on or Azure SQL database then the BizTalk WCF-SQL adapter has some limitations. This is a consequence of not being able to enlist distributed transactions.
