Linked server to foxpro - order by causing error

We're playing with linked servers to hopefully help us ease the migration
from fox to sql. The first problem i've run into is with the "order by"
clause. When running it against our test table, i get the following error:
An error occurred while preparing a query for execution against OLE DB
provider 'VFPOLEDB'.
[OLE/DB provider returned message: Syntax error.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandPrepare: [Ton
gue]
repare returned 0x80040e14].
query:
select [year],uniqueid as b from geoff...test1
where uniqueid > 340000
order by b
uniqueid is just an identity (or equivalent). I've tried several variations,
ordered on different columns, not used an alias, etc.
This works:
select [year] from geoff...test1
where uniqueid between 340000 and 400000
group by [year]
order by [year] desc
Anyone have any ideas?
Thanks in advanced for any replies, and let me know if there is a better
group for me to post this in.Hi D,
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I created a table in the VFP Northwind database called TestYear (UniqueID
Integer AutoInc, Year Integer) and added a few rows. I issued the following
query with no problems at all:
Select [Year], UniqueID As B From VFP_Northwind...TestYear
Where UniqueID > 3
Order By B
I was also successful when the brackets around Year were removed. VFP is
rather forgiving when reserved words are used.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:70406F79-1ACA-4CD6-98C5-AD31173EB4E1@.microsoft.com...
> We're playing with linked servers to hopefully help us ease the migration
> from fox to sql. The first problem i've run into is with the "order by"
> clause. When running it against our test table, i get the following error:
> An error occurred while preparing a query for execution against OLE DB
> provider 'VFPOLEDB'.
> [OLE/DB provider returned message: Syntax error.]
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandPrepare: [T
ongue]
> repare returned 0x80040e14].
>
> query:
> select [year],uniqueid as b from geoff...test1
> where uniqueid > 340000
> order by b
> uniqueid is just an identity (or equivalent). I've tried several
> variations,
> ordered on different columns, not used an alias, etc.
> This works:
> select [year] from geoff...test1
> where uniqueid between 340000 and 400000
> group by [year]
> order by [year] desc|||Hi D,
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I created a table in the VFP Northwind database called TestYear (UniqueID
Integer AutoInc, Year Integer) and added a few rows. I issued the following
query with no problems at all:
Select [Year], UniqueID As B From VFP_Northwind...TestYear
Where UniqueID > 3
Order By B
I was also successful when the brackets around Year were removed. VFP is
rather forgiving when reserved words are used.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:70406F79-1ACA-4CD6-98C5-AD31173EB4E1@.microsoft.com...
> We're playing with linked servers to hopefully help us ease the migration
> from fox to sql. The first problem i've run into is with the "order by"
> clause. When running it against our test table, i get the following error:
> An error occurred while preparing a query for execution against OLE DB
> provider 'VFPOLEDB'.
> [OLE/DB provider returned message: Syntax error.]
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandPrepare: [T
ongue]
> repare returned 0x80040e14].
>
> query:
> select [year],uniqueid as b from geoff...test1
> where uniqueid > 340000
> order by b
> uniqueid is just an identity (or equivalent). I've tried several
> variations,
> ordered on different columns, not used an alias, etc.
> This works:
> select [year] from geoff...test1
> where uniqueid between 340000 and 400000
> group by [year]
> order by [year] desc