query data from the linked foxpro database

hello,guys,

my question is :
how do I query data from the linked foxpro database?

more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:\foxpro object\.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.

sample:

select * from fox..sysu

then the message is:
server: message 7313，level 16，status 1，row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'

I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.

Thank you very muchHi marydan & welcome to dbforums

it could be how you've formed the SQL statement but methinks you've prob tried a few variations & considered four part three part naming conventions ??

I've no experience with a linked foxpro server i'm afraid but it looks like your MS OLEDB for ODBC Provider is saying 'lacking the power to produce a desired effect'

Have you tried setting up a system DSN with the ODBC manager & clicked TEST Connection ?.The Linked server could use the System DSN for the Connection String.

Check the version of the provider DLL for the latest version.


You can use variables with OPENQUERY by using Dynamic SQL (Hisss Spit)


DECLARE @.SQL varChar(4000)
SET @.SQL ='SELECT * FROM OPENQUERY([LinkedServerName],
SELECT 1 FROM [table] WHERE [field] = '+ @.ReqVal +''')'
EXEC(@.SQL)


Have Fun with the single quotes it's V frustrating - trial & error doubling them up.

GW|||thank you ,but other questions.

I'm sorry I may not describe the process clearly.

the process of link to foxpro server is as follows:

first,I create a system dsn named 'fortest',the drive option is 'microsoft visual vfp foxpro',the database type is dbf,then I choose the directory of dbf files as 'd:\foxpro object\' in the path option.

then,I create a linked server in sql server ,the data source is the 'fortest'.
The link is successful ,and I can see the table list on the right.

the first question:

you suggest to use a connection string.
But it's ok when I linked dbc files.
for example ,I linked to a dbc file:yuanyuan,then I can use this clause to get the result.
select * from fox.yuanyuan..szszj
I can get the result ,it's right.
but why can't when linked to dbf files?

the second question:
DECLARE @.SQL varChar(4000)
SET @.SQL ='SELECT * FROM OPENQUERY([LinkedServerName],
SELECT 1 FROM [table] WHERE [field] = '+ @.ReqVal +''')'
EXEC(@.SQL)

if @.ReqVal is char type,how can I forum the sql state?
select * from openquery([fox],‘select * from szsz where id = 'juij'’)
it's wrong of course.

so ,may you give me more help about this two question?

thank you very much for your kindness|||Heres a Step by Step Guide for setting up Linked servers & testing them for both Foxpro .DBC & .DBF Files http://support.microsoft.com/kb/207595

note the recommendations on which Providers to use and their potential problems

refselect * from openquery([fox],select * from szsz where id = 'juij')
it's wrong of course.

Answer = doubling them upid = '''' + @.BadChoiceOfValue' + '''')'

PRINT the @.SQL before executing it and you will see the output - it should copy & paste into a new qury window & work on it's own.

Good Luck - sorry I'm not familiar with FoxPro linked Servers

GW|||Thanks ,Gwilliy.That's it.

You are so kind.

best wishes

:-)