>I'm currently using VFP database, but would like to convert to SQL server soon. I do a lot of coding using the VFP SQL statements. Will I need to change these VFP SQL statements to be useable for SQL server database? How should I code database access now in VFP using VFP databases so that when I am ready to convert, not much code change is needed?
>
>Here is a sample of my VFP select statement as I have it now (with VFP table):
>SELECT * FROM table1 WHERE field1='DONE'
>
>How would this be changed to work with SQL server table?
>Thank you all!

Your example will run on SQL Server without modification. The major differences are:

1. Aggregate queries (GROUP BY)

In VFP you can have a query that doesn't use an aggregate function on fields that are not part of the GROUP BY field list. For example:

SELECT customer.name, customer.address, store.location, sum(sales.balance) as total_balance
FROM customer INNER JOIN address ON address.customerno = customer.customerno
	INNER JOIN store ON store.storeno = customer.storeno
	INNER JOIN sales oN sales.customerno = customer.customerno
GROUP BY customer.name, store.location

Will not work in SQL Server. All non-aggregate columns in the SELECT field list must be included in the GROUP BY field list.

2. Numbered columns

In VFP you can specify ORDER BY and GROUP BY using column numbers instead of field names. In SQL Server the field names are required.

3. VFP specific functions

Most VFP functions have an equivalent in SQL Server, but some do not. In addition, the equivalent functions are usually spelled differently or have a different parameter order.

4. DATE and DATETIME data types

SQL Server only has a DATETIME data type. You cannot add an integer to a date like you can in VFP.

5. LOGICAL field type

SQL Server uses a "bit" field type instead of logical.
.T. = 1, .F. = 0, .NULL. = NULL


To become familiar with SQL Server syntax, you can look at the SQL Server help file "SQLBOL.CHM" on Microsoft's web site. You can also find a copy of MSDE, which is included in Visual Studio. MSDE is a complete SQL Server engine excluding the Enterprise-level tools and features.

Good Luck!

Keith