Order by not working for linked server
-
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:
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 run into this before? Thanks in advanced for any replies
-
select
[year],
uniqueid as b
from geoff...test1
where uniqueid > 340000
order by uniqueid
-
didn't work
-
I thought the same as Pam originally, that it was your alias that was causing problems.
I don't have this problem with my Oracle linked servers so I assume it is a foible of the Foxpro ODBC driver, I'd recommend looking at that in Google Groups first...
However, one thing you could try is ordering by the column ordinal instead eg order by 2, that might do it.
Otherwise I'd suggest that there is something in the datatypes that might be the problem, try different queries with different datatypes to see what happens.
Regardless, if you're looking at migrating I'd suggest that actually all you want to do is populate tables and therefore order by is pretty irrelevant....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply