Thanks Greg,

I am going to investigate a polling solution.

"Greg Linwood" <> wrote in message
> Hi Andrew.
> Unfortunately, you'll find it hard to do memory ysis when running
> under sp_OACreate. SQL Server uses a User Mode Scheduler (UMS) which
> abstracts sql processes (spids) from windows threads which makes your life
> hard in even identifying where to start monitoring things like memory
> in a VB .dll. This on it's own is a good reason to work with out of
> components if you're going to do SQL / COM integration as you can use
> standard sdk tools more easily. I did read your notes on your experiences
> with this (slow) so I won't continue down that vein right now.
> There are many reasons why VB code throws OOM errors and I'd say you'd get
> better response to the VB aspect of your question from a VB newsgroup, but
> perhaps you've just n the heap / stack? I'd love to point you to the
> SDK's HeapWalker but this would be hard given that you're running in
> process... :c/
> I can only make a few further general suggestions, perhaps someone else
> might have some further ideas though:
> (a) Try turning off Retained in Memory and see if the dll is unloaded
> a period of inactivity. This will indicate whether tear down (set nothing)
> calls are effective or not. If the .dll remains loaded for long periods
> after no use (eg 20 mins) you'll have an indication that destructor
> (uninitialize) logic is failing.
> (b) Try running the components under COM+. This would just provide you
> another way of observing the internal workings (eg running instances) of
> .dll at run-time. I know that this means running out of processes with SQL
> Server, but it would be less of a performance penalty than simply going
> ActiveX.exe.. hey - I'm just trying to come up with ideas though! <g>
> (c) Are you actually calling sp_OADestroy? If so, are you checking the rv
> for success?
> (d) Do you have logging infrastructure in the .dll that can write
> / uninitialize events to a file so you can be sure they occur?
> Lastly, although this probably isn't what you want to hear right now, I'd
> seriously considering reversing the architecture. So often it's tempting
> try and make everything happen in real time using triggers / COM
> etc. But sometimes this is just overkill. Triggers should always be
> engineered for efficiency and designing COM into triggers is usually a bad
> idea. If you can live with a polling solution, you're likely to have the
> benefit of a simpler, more lightweight architecture than will scale better
> than sledging COM servers into triggers. Consider the fact that a good
> polling design allows extraction of multiple records at once - utilising
> this fact can often mean that a polling solution is more efficient than a
> trigger / COM solution.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Andrew Rowlings" <> wrote in message
> news:OTYKSwsPDHA.704tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I have an ongoing problem where I get an error that only says "Out of
> > memory" (the error number is something generic like -217364578). The
> > machine has 4GB RAM and there is always available RAM, so "Out of
> > does not seem to apply in a system-wide context.
> >
> > The architecture of my solution is ...
> >
> > In a VB.NET windows application data comes in on a socket. The data is
> > pd and a stored proc exec string is constructed. It is submitted to
> the
> > SQL Server database via the following type of code...
> >
> > sSql = "Exec upInsDistribute.... blah blah etc. "
> > Dim callback As New Threading.WaitCallback(AddressOf ExecSQLOnNewThread)
> > Threading.ThreadPool.QueueUserWorkItem(callback, sSql)
> >
> > ExecSQLOnNewThread is a method that opens a connection and submits the
> > string, thus achieving asychronous database interaction and allowing the
> > application to continue.
> >
> > The stored procedure inserts data into a table that has an insert
> > The trigger invokes another stored procedure that contains OLE
> > stored procedure calls that invoke a VB6 dll that I wrote (Unattended
> > Execution and Retained in Memory both checked on, and threading model
> to
> > apartment). The VB6 dll has a reference to the domobj.tlb type library
> > which provides COM access to Lotus Notes databases. The table provides
> > audit of what was distributed to lotus notes databases and the trigger
> > allows the updates to happen as and when the data is received rather
> > writing some agent type program that runs every x seconds to do the
> > distributing.
> >
> > Every thing works great except every two or three weeks the VB6 dll
> > returning the "Out of memory" error and due to the structure of my error
> > handling in the dll, I feel certain that this message is coming from the
> > Lotus Notes COM Objects. I then have to stop and restart SQL Server in
> > order to get the solution performing correctly again. Nothing in the
> event
> > log, or sql server logs gives it away.
> >
> > The OLE Automation I use is in-process, via calls like ...
> >
> > EXEC hr = sp_OACreate 'NotesCOMInterface.OrderEntry', NotesSession OUT
> >
> > I have tried out-of-process (with 4 supplied as the 3rd parameter to
> > sp_AOCreate and dll re-compiled as an ActiveX EXE) and it failed
> miserably -
> > this is a high-transaction system, and it ground to a halt with
> > out-of-process invocation, so it is not an option.
> >
> > How can I find out if the "Out of memory" error is arising because of
> > limitation or problem in the OLE Automation environment of SQL Server?
> >
> > Of course, perhaps it is an issue with the Notes COM objects, and thus
> > relevant to this newsgroup, but I don't know how to tell. I have done
> some
> > research on these objects and was unable to find any related issues.
> >
> > Thanks for any help,
> > Andrew.
> >
> >