How to handle VFP6 remote view when SQL server is switched?

[roselover]

I'm not sure what I'm doing wrong with this problem. I have data on my company's SQL server.

I have a VFP6 database in which I create a connection using an ODBC source I created.

I create a view in the VFP database based on that connection. When I 'use' the view, it works fine.

At the client's site, I create the ODBC source and in a program the user runs, I re-do the connection (same name) with the following code:

open database soil1
create connection olayconn datasource &mdsOlay userid &msqllogar password &msqlpassar

I know the variables are correct, but when I 'use' the view I had defined initially, it won't work on the clients' site although the table structures are identical. I know I have an issue with there being different owner names on the SQL server between my site and the clients' and I'm sure VFP is getting confused.

What do I need to do to make this work on both my network and the client's network?

 

[jesylv]

I suppose you checked the visibility of your mssql server from your client's site. Try pinging your server from your client's network using your server ip address.



Jean

http://www.sigp.ca

 

[Mike Lewis]

Hi Roselover,

There are many possible explanations for this. It's impossible to diagnose it without more information. In particular, what exactly happens when you try to run it at the client's site? What error do you get? At what point does the error arise?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[ChrisCarroll]

If you know for a fact that the owner names are different then that is likely the cause of the problem and I don't think there is a way to write one view that works on both servers.

Sql Server always names its tables as ownername.tablename. A typical VFP view, 'behind the scenes' is defined as

Select * from owername.tablename tablename

ie the owner-qualified name is aliased to just tablename.

One way to cope might be to automate a process for converting all references in your views from company SQL owner to client SQL owner.

1) Use GenDBC on your company DBC to create a PRG that will recreate the DBC.
2) Use something like

StrToFile( StrTran( FileToStr("GeneratedDBC.prg", "companyowner.", "clientowner.")),"ClientDBC.Prg")

to convert all references to your companay SQL owner name to client SQL owner name.
3) Run the resultant ClientDBC.Prg to generate a DBC that will work on the client site.

I'm fairly sure this should work reliably, _although_ I vaguely recall a rumour that earlier versions of GenDBC.prg weren't perfect so you'd want to get a copy from someone with a more recent VFP version.

 

[roselover]

Thank you, ChrisCarroll. I was unaware of the Gendbc.prg and I think that will do the trick!

Alysa