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

:-)

Query constantly failing.

Hi Guys,
I am trying to do a simple bill run on one of our SQL servers and it keeps
terminating prematurely with the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (rec
v()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation
The query used to run fine, nothing has changed hardware wise and the server
seems to have no obvious problems. Does this sound like KB article: 872968?
If not, what do you belive this issue can be?
Thanks for all your help guys!Hard to say if it's related to the KB article - just not
enough info to say. Have you tried running a trace or
running profiler while you execute the query? Did you check
the SQL Server error log for more information?
-Sue
On Wed, 5 Jul 2006 04:41:02 -0700, Jonny
<Jonny@.discussions.microsoft.com> wrote:

>Hi Guys,
>I am trying to do a simple bill run on one of our SQL servers and it keeps
>terminating prematurely with the following error:
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (re
cv()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation
>The query used to run fine, nothing has changed hardware wise and the serve
r
>seems to have no obvious problems. Does this sound like KB article: 872968?
>If not, what do you belive this issue can be?
>Thanks for all your help guys!