First of all, Thanks "Cowboy" for your response.
Why am I using ADO instead of ADO.net? Well, the problem is that I am
using BizTalk Server 2002. These are old COM objects and I believe
they are excepting ADO. You will notice that I fill the record set
using Microsoft.BizTalk.BTSConfig.
i.e.
Dim BtConf As New Microsoft.BizTalk.BTSConfig
Dim rs As ADODB._Recordset
rs = BtConf.Channels
Okay, I understand a bit more. I have worked more extensively with BizTalk
2004 (with some 2006). Not an expert by any means, but I have played quite a
bit.
Most of the work I have done with BizTalk has stayed firmly in the realm of
XML. I have had to straighten out a few implementations that tried a "commad
at a time" type of implementation, which is why I asked if you could chunk.
BizTalk 2002 is a completely different creature.
One suggestion I have is asking in the biztalk groups, as well. It is
possible someone else has played with the code.
These are supplied by Microsoft, and the example code I got from them,
uses ADO. I am not real familiar with ADO vs ADO.net. Can I use
ADO.NET when the com object is expecting ADO? If so, do you have an
example of how the above could be changed?
I had already tried commenting out the RaiseEvent statement thinking
that it could have caused the problem, it had no effect.
Your next question, What is this doing ...
What this is supposed to do is Refresh BizTalk after a change has been
made to the BizTalk Channels. Without doing this, BizTalk continues to
use the Channels prior to my changes.
Can I do more records at a time rather then single threading them?
I wish!. I have not been able to find a way to accomplish that. I
got this code out of a SDK supplied by Microsoft called BTM_Refresh.
By the way, I have the same problem running the Microsoft BTM_Refresh
program, that is why I am trying to rewrite it.
It is beginning to look more and more like the marshalling of COM objects is
placing you in a spot where memory is not being garbage collected and
causing your issue. One possibility would be refreshing the BizTalkChannel
object every X iterations. Something like:
Do While Not rs.EOF
rs_count += 1
RaiseEvent BizTalkProgress(rs_count & " of " &
rs_RecCount & " " & rs.Fields("Name").Value)
obj.LoadByName(rs.Fields("Name").Value)
obj.Save()
'Not sure syntax is correct (not tested)
'I am more of a C# person
if (rs_count MOD 1000) = 0 Then
'refresh object here
obj = Nothing
obj = DocConfig.CreateChannel
end if
rs.MoveNext()
Loop
This is a shot in the dark, but worth a try. I have written another idea in
later which follows the same type of logic.
My thought is that in my loop when I execute the "obj.LoadByName" and
then "obj.Save" over and over, it is keeping each iteration in memory
and not releasing it until I exit my program. This is the spot I tried
to run the Garbage collector, but that had no effect either.
This is common. Unless you fully take conrol of the GC (which is really
impossible, although you can get close), you end up with the system still
making decisions for you and not cleaning things up. An option, however,
would be to fire up the object in another thread or a batch of objects in
another thread, and then fire the two methods. You then kill the threads and
allow GC to know everything is finalized.
The issue here, as I see it, relates to the way .NET and COM differ. In COM,
you have a reference count. When it reaches 0, the object is immediately
destroyed. In .NET, you have a series of passes at the objects to destroy
those with no references. If pass 1 frees enough memory on the machine, the
GC stops to avoid interference.
Now, here comes a particularly tricky part (I have not tested this, so it is
just theory). If you are on a machine with plenty of memory, can you exceed
the memory space of the program because GC is just concerned with the total
memory space? If so, one option you might have is increasing the virtual
memory space for processes on the machine in question. NOTE, that this is
not a cure, just a reprieve. If your workload continues to increase, you
will ultimately hit the limit again, forcing you to look for other
solutions, but it might get you through the interim.
One thing i would consider is downloading the Process Explorer from
sysinternals (or a similar tool) and watching what happens when the program
runs. It is not the best at watching .NET (will show some information), but
it may give a clue if a bunch of objects are floating around.
The only work around I have found so far, is to keep track of where I
run out of memory, write a record with that count, exit my program and
then restart the program from the point that it got the exception.
This seems to work, but boy is that ugly.
One option you might try (not golden, but it might chunk things up enough),
is to grab the first X records in ADO and process them. You can then exit
out to ensure everything clears (similar to your "wait until the out of
memory error fires" solution, but proactively shutting down the process
rather than letting it bomb).
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside of the box!
*************************************************
