Tried it again now...and the image comes up ok.

I see in the diagram that you have VFP databases 'LINKED' to Sql database.

Does this just mean your application us using some data from Sql and some data from VFP, or is there something else 'magical' going on

Hi William.

I ahve a problem clicking on your link. When I click, all I get up is a window with all the threads again. (I think I have got this with other links here as well). Should I be able to click on it and see an image ?

Hello Gerard,

Here is a link to a design schematic I'd like to develop:

http://forum.strataframe.net/Attachment1611.aspx

Here is a link to the dicussion:

http://forum.strataframe.net/Topic13841.aspx

Basically, it would be nice if I could treat a VFP database on each side of a WAN connection as a Linked Server, giving my StrataFrame application access to the VFP data on each side simultaneously.

I've run into problems attempting to "chain" linked servers (as depicted in the schematic) and the only solution I've been able to arrive at so far is to have multiple SQL connections in my SF app. One for each SQL server.

Hi William.

I have seen a few references to 'Linked Servers' here and wonder what they are and in what circumstances you would use them

Thanks for the additional info. I don't have any solutions at this time though

Hi Greg,

This solution is not ideal, but yes, it works. I would much prefer finding the right view definition that would work properly with VFP DATETIME parameters (if that indeed exists).

The problem is that it precludes me from using an SQL Server View based on the linked table. The OPENQUERY method complicates passing parameters, and seems to be problematic for chained linked servers.

Thanks.

I was poking around and decided to investigate this. Turns out William got an answer over on a msdn forum.

The answer is to use OpenQuery, which passes the query to VFP, which can optimize the query. Apparently SQL Server can't do that on datetime fields with a linked VFP server.  Here is the new code from the post:

select * from openquery(VCAL, 'select * FROM csHearin WHERE he_tdthearing = {06-02-1992 02:30 PM}')

Hello,

This is not a SF question, but I'm hoping someone may have run into the same issue...

I have a VFP linked server in SQL2008 (the databases/tables are all on the same workstation)and am trying to optimize a query that filters on a datetime field. The VFP table has an index on this field, but I'm not certain how to speed up my query.

For example:

SELECT *
FROM MyVFPLinkedServer...MyTable
WHERE
MyDateTimeField >= '03/01/2011 00:00:00' AND
MyDateTimeField <= '03/31/2011 23:59:59'

This returns 75 rows and takes 30 seconds to execute.

Filtering on an integer field in the same table is very fast, so I guess I'm just not formatting the WHERE clause properly to take advantage of the DATETIME field index.


Can anyone suggest how I can speed up the linked server query with DATETIME values?

Thanks.