Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005

    Question Unanswered: SQL Server linked table to dbase III files


    I am trying to use linked tables to connect SQL Server 2000 to a legacy system using dbase III files. (I need real time read only access to these files)

    I have created a linked table from SQL server to a folder on the C drive which contains the dbase III files, using an ODBC DSN which uses "Microsoft dbase driver (*.dbf)". DSN tested successfully using Excel. Linked server connection is then created using "Microsoft OLE DB Provider for ODBC drivers".

    The dbase tables appear OK in Enterprise manager, but I cannot get a query to work in SQL Anayzer, using the 4-part name syntax.

    My query is just :
    SELECT * FROM LinkedTable...customers

    Error message is "Invalid schema or catalog specified for provider 'MSDASQL'". Now I am pretty sure dbase files do not support any sort of schema / catalog set up, so I suspect SQL Server is looking for something it is not going to get.

    One clue might be that in Enterprise manager, under the catalog column, I get the pathname to the dbase file, ie c:\customers.dbf, which I cannot enter in the 4-part syntax.

    Any suggestion welcome !!!

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    try using openquery. look it up in BOL.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2005

    Thanks for that suggestion - it does not work using OPENQUERY, but this idea led me on to OPENROWSET which does work, as in :

    SELECT *
    'MSDASQL','DRIVER={Microsoft Dbase driver (*.dbf)}',
    'SELECT * FROM C:\customers.dbf')

    I would still like to know how it can be done using linked server, rather than this adhoc solution, if anyone has any ideas...

Posting Permissions

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