I have an application that uses a SQL Server 2000 database. I want to create a VFP 9.0 dbc that contains a remote view for each table in SQL Server. I plan to use remote views to access the data. I need to create a dbc to match the SQL Server database. The SQL Server database was originally created from a VFP app and we use remote views to switch our application from vfp tables to SQL Server. Someone added several tables & fields to the SQL Server database and we would like to have a VFP app create the dbc from SQL Server rather than manually go thru each table.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Server 2000 to VFP 9.0 DBC
- Thread starter WIREMESH
- Start date
Creating the Remote Views of the tables programatically with VFP is not a difficult task.
The initial challenge will be to get a list of the SQL Database Table names with which to use in your VFP code.
There are some web references such as:
which might offer some help or you might ask in the Tek-Tips SQL Server forum(s).
If you get a workable method, you could use it to create one more SQL Server table containing the names of the tables (or export the table name results to an external file of some sort) and then use it to 'drive' your code.
One caution about using Remote Views for accessing SQL Server tables...
In many situations Remote Views work well, but I have found some instances where, due to a variety of circumstances, the Remote View would not accomplish the needed tasks. In those instances I had to change methodology to SQL Pass Through in order to get the work done.
Good Luck,
JRB-Bldr
Code:
* --- Create New Database ---
CREATE DATABASE <NewVFPDBCName>
* --- Create Named Connection To SQL Server ---
CREATE CONNECTION [ConnectionName | ?] [DATASOURCE cDataSourceName]
[USERID cUserID] [PASSWORD cPassWord] [DATABASE cDatabaseName]
| CONNSTRING cConnectionString]
* --- Now Loop to build all Remote Views ---
USE RefTable
SCAN
SQLTableName = ALLTRIM(RefTable.TableName)
CREATE SQL View <ViewName> ;
REMOTE CONNECTION <ConnectionName>;
SHARE AS SELECT * FROM <SQLTableName>
* --- Then add whatever CURORSETPROP() or DBSETPROP() commands to modify the View as needed. ---
ENDSCANThe initial challenge will be to get a list of the SQL Database Table names with which to use in your VFP code.
There are some web references such as:
which might offer some help or you might ask in the Tek-Tips SQL Server forum(s).
If you get a workable method, you could use it to create one more SQL Server table containing the names of the tables (or export the table name results to an external file of some sort) and then use it to 'drive' your code.
One caution about using Remote Views for accessing SQL Server tables...
In many situations Remote Views work well, but I have found some instances where, due to a variety of circumstances, the Remote View would not accomplish the needed tasks. In those instances I had to change methodology to SQL Pass Through in order to get the work done.
Good Luck,
JRB-Bldr
Mike Lewis
Programmer
Wiremesh,
Just to add to JRB-Bldr's suggestion. Use SQLCOLUMNS() and SQLTABLES() to get the names of the columns and tables from the server, and also the data types, etc. of the columns. This will give you the information you need to construct the CREATE SQL VIEW statements.
As an alternative approach, you can use VFP's upsizing wizard to create the views. You will need a copy of your original tables in VFP, which you will need to modify to take account of the changes you mentioned.
You can run these tables through the wizard to create a dummy set of upsized tables, which you then discared. The point is that the wizard has the option of creating remote views for each table it upsizes. This will give you what you want, with the advantage that it involves no programming.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Just to add to JRB-Bldr's suggestion. Use SQLCOLUMNS() and SQLTABLES() to get the names of the columns and tables from the server, and also the data types, etc. of the columns. This will give you the information you need to construct the CREATE SQL VIEW statements.
As an alternative approach, you can use VFP's upsizing wizard to create the views. You will need a copy of your original tables in VFP, which you will need to modify to take account of the changes you mentioned.
You can run these tables through the wizard to create a dummy set of upsized tables, which you then discared. The point is that the wizard has the option of creating remote views for each table it upsizes. This will give you what you want, with the advantage that it involves no programming.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
- Status
