Calling Visual Foxpro 7 COM

Dear Experts,

We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).

We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.

The Automation manager connection doesn't release all the connections.

What effects is this having to our SQL Server 2005 installation and how can we avoid it?

We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.

Thank you for your time.

Regards,

Spyros Christodoulou

You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,

Thanks for your reply.

Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.

The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)

For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.

Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?

Regards,

Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,

Thanks for your reply,

I found a post you made at microsoft.public.sqlserver.programming on august of 2001.

"I have used this. It works perfectly fine. Are you making a EXE? Does it

have UI? Make sure it doesn't have any of those. Here is one sample:

NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE

NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE

-- Now run this on the SQL Server

declare @.o int, @.h int, @.p varchar( 255 )

exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.o

exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.p

exec sp_oadestroy @.o"

I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)

Do you know how to make it an out of process and called it based on the following scenario:

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

Any help will be greatly appreciated.

Thanks again,

Spyros Christodoulou

|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||

Hi Umachandar and Spyros:

I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.

The remaining problem is also similar as the one Spyros described:

"(...)

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

(...)"

In server 1, I have the COM+ application o package correctly installed and working since years

In Clients computers various front end consume the server classes via, for example in VB

CreateObject('AppMastervs.cOrga', mtsServer1)

or in VFP

CreateObjectEx('AppMastervs.cOrga', mtsServer1)

These clients has registered the corresponding .vbr & tlb via CliReg32

I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5

Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.

So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.

Thanks in advance;

Claudio Facundo Lacivita