Armando582596


Hi guys I need to merge some VFP tables from our intranet with an external sql server.

Is this possible any advice

Thanks



Re: Visual fox pro table

CetinBasoz


Probably it's possible but I'm not clear what do you mean by saying "merge VFP tables with an external SQL server". What do you mean by "merge" here and what do you mean by "external" SQL server




Re: Visual fox pro table

Armando

Sorry I didn't explain myself properly above.

This is the first time that I work on this environment.


They want to ship daily copies of the FOX PRO tables from an intranet to an external SQL server (located in a hosting center).


I know I can export them to sql server and setup replication between them
But we want to skip this step.
How would you guys do it
Thanks in advanced








Re: Visual fox pro table

CetinBasoz

Still it's not very clearSmile

Anyway you have tons of options, from VFP side, from SQL server side, from a 3rd side (asp, asp.net, VBA, .Net etc).

-From VFP side you can connect to SQL server in a variety of ways (RV,SPT,CA,ADO) and insert - insert bulk/update/delete/select directly or call an SP. You could as well use XML and web services as a publisher or consumer.

-From SQL server side, you can add VFP tables as linked servers or use OpenRowSet(), OpenDataSource() functions to directly read from VFP tables (in SQL2005, require surface area configuration either using configuration tool or in code). Or more elegantly you could create a DTS or SSIS job to do that daily.

-3rd option is straightforward also. An asp/VBA code could connect to both (to VFP only use OLEDB connection, ODBC option is out) and transfer as needed. With .Net it's even much more easier with the SqlBulkCopy class.

Details would differ depending on from where you'd do it, how you'd do it (ie: simply add all records, or check against existing keys etc).

PS: As I understand you do not want them to directly use SQL server as backend (and even if you do they need to do modifications to write into SQL server instead of VFP tables).





Re: Visual fox pro table

Armando

Thanks for your reply.


My concern is security, as the foxpro tables are behind a firewall and sql server is in hosting center.
Is it possible to add VFP tables as linked server outside the firewall
What are the security implications of doing this

Now, in terms of performance and concurrency
is it feasible to access the fox pro tables in "real time" from a .net web site (production)

Thanks for your help.
I appreciate it.




Re: Visual fox pro table

AndyKr

>> My concern is security, as the foxpro tables are behind a firewall and sql server is in hosting center.
Is it possible to add VFP tables as linked server outside the firewall

No you cannot add VFP Tables to SQL Server as a linked server. However you can use an OLEDB connection to mimic a "linked server" and then access it using OPENQUERY.

>>Now, in terms of performance and concurrency is it feasible to access the fox pro tables in "real time" from a .net web site (production)

Sure its feasible. There are loads of sites using VFP Tables as their back end - more than you would realize, or even expect. Again, use OLEDB to access the data from the page.






Re: Visual fox pro table

CetinBasoz

You can add it as a linked server. But adding as a linked server woudl only let you select (doesn't support modification - I think that is why Andy says you can't add as a linked server. Not a usefull link really and firewall adds obstacles).

Yes it is feasible but since tou have SQL server you would want to use that instead.





Re: Visual fox pro table

AndyKr

Yes Cetin, thank you for clarifying - I was rushing this morning and didn't re-read what I had written.




Re: Visual fox pro table

Armando

I added VFP as a linked server as suggested but when I tried to run distributed transactions (dumping data into a temp table from the linked server) I get this error

OLE DB provider "SQLNCLI" for linked server "APOLLO01" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 5

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "APOLO01" was unable to begin a distributed transaction.


The Transaction cordinators are enabled in both server.

is this supported




Re: Visual fox pro table

CetinBasoz

No, as I said before only select.