ffarzad321 said:
I think remote view work on server and send the result to client.
No, that's only half the truth. Like local views remote view definition (and the query itself) are stored in a VFP DBC. Remote views differ from local views by making an ODBC connection to a remote database.
Now, in case you make a folder of DBFs the "remote" database - which is possible by using the VFP ODBC driver - you don't connect to a remote server, the ODBC driver itself is "the server" and queries DBFs with the VFP SQL engine embedded in the ODBC driver. This surfaces in the fact on how you define the ODBC connection with a path to a DBC or a directory of free DBFs with a path either using a mapped drive or a UNC path. LAN is the limit, you don't connect to a server but to a share.
You could talk of remote execution if you'd manage to install the VFP ODBC driver on the file server with the network share, but then please tell me: How do you make an ODBC connection to a remotely installed ODBC driver? The ODBC driver used to connect to a database has to be locally installed at the client, that's also the case with MSSQL or MySQL servers, etc. There are always client side drivers the local software interacts with.
The difference of such real database servers is, that the ODBC driver to server databases doesn't act as the sql engine itself as the VFP driver, it is just an agent talking to the remote server and receiving results from it.
And there you also have the way an MSSQL server helps you to really make the query execution remote, by using MSSQL as the server component.
And no, you don't need to create MSSQL tables. Surely that would open up many advantages and features MSSQL can do with its own storage engine, but to explain the term "linked server" a bit more: It has the historic background that at first you could use an SQL server instance on one database server to access another SQL instance instead of migrating the database. The "links" now are not only limited to real servers, but any data source/sink which can be defined by ODBC or OLEDB.
So in this way you actually get what I said is not possible directly. The VFP ODBC driver or the OleDB Provider is installed on the server side, the MSSQL server on that server (computer) uses it, and your VFP frontend addresses this construct by an MSSQL ODBC driver which menas you go through the three agents (or brokers) - 1. the MSSQL ODBC driver (clientside), 2. the MSSQL server (server side), 3. the VFP ODBC driver or OLEDB provider (server side).
So only that can give you real remote execution of queries to DBFs.
What is true is that remote views execute queries remote in the usual case the remote backend is not VFP but a server, a database server/service, not just a file server.
When you fully move to MSSQL databases you spare one level of agent on the path from frontend to data, but that'll take time to rewrite. The linked server means you keep the folder of DBFs (free or part of a DBC doesn't matter) and so you can use that in parallel to native USE of the DBFs, you stay with DBFs and can continue to run legacy code needing them but also have a remote server that can act for you via MSSQL ODBC.
In summary the needed ingredients are:
1. Your DBFs where they currently are - provided it's a Windows Server allowing to install MSSQL in parallel.
In theory this can be on another computer, but that makes DBF access networed additional to the networked access of MSSQL itself. For best performance the DBFs should be local to the MSSQL server, just like its native databases are local to it (disregarding more complex scenarios with SAN, for example).
2. An SQL Server Express insance installed on that same server. (32bit version)
3. A VFP ODBC driver or the OleDB Provider (32bit)
If you find third party 64bit drivers/providers - fine, then you can also let a 64bit MSSQL Server act on your DBFs
4. A 32bit ODBC driver for MSSQL access on the client running your VFP EXE.
If you have your DBFs in a NAS (usually Linux based) there's a low chance you can use Microsofts Linux version of MSSQL Server, but AFAIK only a full blown Linux server will give you enough freedom to install that.
I haven't looked but it's still no problem to get 32bit MSSQL server and drivers, just note a 64bit version of MSSQL usually is bundled with 64bit client tools (the MSSQL ODBC drivers, for example) and the VFP exe needs a 32bit ODBC driver. A client side 32bit ODBC driver can also address a 64bit MSSQL server instance through network.
But you face a problem with a 64 bit MSSQL instance - not having a 64bit ODBC or OleDB Provider to the DBFs to be used by the MSSQL Server instance. So the simplest scenario is to have all 32bit software and you can use an older SQL edition for that matter, as you make no major use of the MSSQL database and storage engines. You merely use it as a server side gateway to the DBFs.
Chriss
