>>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.

Yes, it would run but result could be different from VFP. In VFP with SET ANSI OFF, wich is default, the above select would pull all records that have "DONE" at the begining of the field1 field. In Sql Server only records that have exactly "DONE" in the field1 would be selected.

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.

The column number can be used in ORDER BY but not GROUP BY clause in Sql Server.

>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.

You can add/substract an integer to a datetime value but it would be treated as a number of days not a number of seconds like in VFP.


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

The bit data type is an integer data type. You cannot create index on bit field, neither you can use bit field in GROUP BY clause. The tinyint integer type can be used as logical also.

>
>
>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.