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

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: [Tongue]
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: [Tongue]
> 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

Linked Server to Foxpro

I am trying to setup a linked server from server1 (SQL Server) to Foxpro DBC
files on server2 and I get the following error:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have checked that the account that SQL Server runs as has full permissions
on the folder where the DBC file lives.
I can also setup an ODBC connection and use MS Query to access the data but
SQL will not see it or give me any meaningful error!
If I copy the Foxpro files locally onto the SQL server then it is fine.
Once I have this working then I need to figure out how to create the
triggers to populate the Foxpro DBs!!
Any advice would be much appreciated.
Regards
JamesHi James,
When there are problems on a remote machine that don't show on the SQL
Server it's usually a problem of permissions. You said below that the SQL
Server has permissions on the remote machine, but maybe it's not working as
you expect.
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"JamesA" <JamesA@.discussions.microsoft.com> wrote in message
news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> I have checked that the account that SQL Server runs as has full
> permissions
> on the folder where the DBC file lives.
> I can also setup an ODBC connection and use MS Query to access the data
> but
> SQL will not see it or give me any meaningful error!
> If I copy the Foxpro files locally onto the SQL server then it is fine.|||Hi Cindy,
I looked at the SQL Server service on my server and it is running as
DOMAIN\SQLAdmin and on the folder where the Foxpro files live the account
DOMAIN\SQLAdmin has full permissions and even looking at the effective
permisions on the DBC file, this account has full permissions. Any
suggestions how I can test to see if this is a permissions problem?
Regards
James
"Cindy Winegarden" wrote:
> Hi James,
> When there are problems on a remote machine that don't show on the SQL
> Server it's usually a problem of permissions. You said below that the SQL
> Server has permissions on the remote machine, but maybe it's not working as
> you expect.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
> "JamesA" <JamesA@.discussions.microsoft.com> wrote in message
> news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> > I have checked that the account that SQL Server runs as has full
> > permissions
> > on the folder where the DBC file lives.
> >
> > I can also setup an ODBC connection and use MS Query to access the data
> > but
> > SQL will not see it or give me any meaningful error!
> >
> > If I copy the Foxpro files locally onto the SQL server then it is fine.
>
>