>>This query runs fine in VFP Sql, but it does not work when I try to use it against Sql Server.
>
>One thing I see at first is that you are using .f. syntax which is not supported. You would have to replace that with 0.

Good catch.

I've started writting a method that will convert a VFP Select to a Sql Select. It does not cover every case, but I'm adding to it each time I find something in my situation that I can easily deal with using basic string functions.

I know there is a page on fox.wikis.com that has a comparison page and I am referring to that too as I encounter each case.

Here's what I have so far:

Lparameters tcSql

*-- 2010-02-28: Added this method to fix up my Sql statements...
*-- My Sql statements are written for VFP Selects, so when they run against Sql Server, they need a little work
*-- to adjust them to the correct syntax for Sql Server.

*-- See other ways to handle this here: http://www.west-wind.com/wwThreads/default.asp?Thread=2PP17MHZ0&MsgId=2PR1FFRHX

If this.nDataMode = 2
 tcSql = Strtran(tcSql, 'nvl(', 'IsNull(', 1, 999, 1)
 tcSql = Strtran(tcSql, '==', '=', 1, 999, 1)
 tcSql = Strtran(tcSql, 'substr(', 'SubString(', 1, 999, 1)

 tcSql = Strtran(tcSql, '.t.', '1', 1, 999, 1)
 tcSql = Strtran(tcSql, '.f.', '0', 1, 999, 1)

 tcSql = Strtran(tcSql, '.desc', '.[desc]', 1, 999, 1) && Sadly, some of my tables use these reserved words as field names
 tcSql = Strtran(tcSql, 'desc,', '[desc],', 1, 999, 1) 
 tcSql = Strtran(tcSql, 'as desc', 'as [desc]', 1, 999, 1) 
 tcSql = Strtran(tcSql, 'by desc', 'by [desc]', 1, 999, 1)

 tcSql = Strtran(tcSql, '.order', '.[order]', 1, 999, 1)
 tcSql = Strtran(tcSql, 'order,', '[order],', 1, 999, 1) 
 tcSql = Strtran(tcSql, 'as order', 'as [order]', 1, 999, 1)
 tcSql = Strtran(tcSql, 'by order', 'by [order]', 1, 999, 1)

 tcSql = Strtran(tcSql, '.view', '.[view]', 1, 999, 1) 
 tcSql = Strtran(tcSql, 'view,', '[view],', 1, 999, 1) 
 tcSql = Strtran(tcSql, 'as view', 'as [view]', 1, 999, 1)
 tcSql = Strtran(tcSql, 'by view', 'by [view]', 1, 999, 1)

 tcSql = Strtran(tcSql, ' ReadWrite', '', 1, 999, 1) && Strip this out. Sql Server queries are automatically ReadWrite.
EndIf

Return tcSql

.