- This topic has 5 replies, 1 voice, and was last updated 9 years, 1 month ago by
.
-
Posts
-
-
Hi everyone, got a real thorn in my side at the moment and am after some help….
Am currently on a project where we are using the xml capabilities of sql 2005 to store entire messages in a single xml column within a table in our database rather than push the data into normalized tables.
The messages are batches which on occasion are quite large (50mb+).
When trying to insert this data into sql server I get a series of System.OutOfMemory exceptions warnings see below in red… followed by the eventual error in the event log.
The adapter failed to transmit message going to send port “p_SQLInsertBatch” with URL “SQL://dctrsql02/DocumentStoreDB_Test//”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Exception of type ‘System.OutOfMemoryException’ was thrown.”.
The content of the error and the fact that I am getting this issue as a set of warnings first points to the problem being with the adapter assembly directly causing the problem. The server has 4gb of memory so it should be able to cope.
I have tried saving the message out of biztalk and submitting it using messaging running the sql send port with its own host instance rather than via my orchestration application to reduce the memory footprint to its are bones, and I still get the message.
Now I think, but am not sure, that because of the way the sql adapter calls a stored proc to insert xml may be the problem. The adapter requires the xml parameter to be passed into it as a string in an attribute, and the adapter maps this to the equivalent stored procedure paramters. A 50mb xml string escaped out has some unfortunate but essential file growth due to escaping of xml characters, so i believe it is this excessivley long string that is crippling the adapter.
Are there any limits on the size of data in a single column that anyone has come across, what is the largest “string” or other data type people have pushed into SQL?
Has anyone any ideas if this is solveable without rewriting the application to some how chop the batch down to size?
I would be particularly intersted in any server configurations that I could try…
Thanks
Dino 2
-
You can review this page and play around with the large message fragment size, TransformThreshold, and large message threshold settings:
-
Have looked at this prior to posting…
The issue is not to do with the large message handling within biztalk, but the fact that the sql adapter pushes xml data to sql using a string attribute. The string ends up at around 50mb which causes the adapter to throw the outofmemoryexception message. At this point I guess the entire streaming model of bts breaks down and we are dealing with large memory consumption. Anyway I think I have cludged my way around it!
-
-
-
I found that run in query analyser the insert query was ok and actually ran incredibly quick.
There are a few techniques.
Alter the database design to save individual messages or smaller batch sizes and submit the data a few rows at a time (kinda obvious and easy, but too me this was ugly, but in the end the solution we plumbed for)
Use the sql bulk load adapter written by . While this works, the company I was working for would not allow an adapter that was not built and tested in house. http://blog.biztalk-info.com/archive/2009/02/18/SQL_Bulk_Load_adapter.aspx
Am on an extended holiday at the mo, so this is all from (hazy) memory.
Hope this helps..
3.
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.