Multi-Database Remote View

[SGLong]

Is it possible to create a remote view in VFP that draws information from two SQL-Server databases on two separate servers (with two separate connection strings)?

Steve

 

[JimWWinter]

Well, you could create 2 views, one from each server, and then a third view based on the first 2, but I can't imagine how you'd ever update such a thing.

Regards,
Jim

 

[Dan Freeman]

Updating multi-table views CAN be done, but it's every bit as much work (more, actually) as updating two different views. <s>

You have to send two updates. First you set Updateable, UpdateFieldsList, etc., for one of the underlying tables and sende that update, then you turn those off and turn on the fields for the other table and then send that update.

The ONLY value to this is being able to manually put the whole shooting match into a single transaction on the back end, but since this thread is about data on more than one server even that goes out the window.

 

[SGLong]

Mike,

I agree with you, and it's not my plan to make this an updateable view --- I just want to be able to access the data for display / reporting purposes without having to jump thru hoops each time I need it.

Steve

 

[fluteplr]

You would be better off creating a Sql Server view on one of the server that accesses both tabes. The one on the other server using a linked server connection. Then create a VFP view on the SQL Server view.

 

[Mike Lewis]

Steve,

What you want to is not only perfectly possible, but not all that unusual. Here's how you would do it (this is off the top of my head and not tested):

CREATE SQL VIEW MyView CONNECTION MyConnection ;
  AS SELECT * FROM FirstServer.MyDatabase.dbo.MyTable ;
  JOIN SecondServer.MyOtherDatabase.dbo.MyOtherTable ;
  ON <condition> ;
  WHERE <condition>

Of course, this assumes that you have the necessary permissions to access both databases.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[JimWWinter]

Mike,

I think the problem here is he has a separate connection string for each database.

Regards,
Jim

 

[Mike Lewis]

Jim,

I think the problem here is he has a separate connection string for each database.

Ah, yes, I see that now.

But I wonder if he needs two connection strings. I would've thought that, provided he has a connection to the first server, and that server can see the second server, all would be well. Assuming, also, that the user is authenticated for both servers. But I might be totally wrong about that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SGLong]

Mike & Jim

I've been playing around with various ways of the "CREATE SQL..." command that Mike suggested without success. I think that fluteplr had the right idea - make a view in SQL that links the two sets of files and then have a VFP view of that view. Thanks for the suggestions. I'll keep you posted.

Steve