Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    17

    Unanswered: 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 much

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi 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)

    Code:
    
    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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Aug 2007
    Posts
    17
    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

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    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

    ref
    Code:
    select * from openquery([fox],‘select * from szsz where id = 'juij'’)
    it's wrong of course.
    Answer =
    doubling them up
    id = '''' + @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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Aug 2007
    Posts
    17

    Thumbs up that's it.

    Thanks ,Gwilliy.That's it.

    You are so kind.

    best wishes

    :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •