Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    27

    Unanswered: Error with linked server

    I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
    Invalid schema or catalog specified for provider 'MSDASQL'.
    OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

    The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
    SELECT * FROM Server...Table

    SQL Server is aparently expecting something like this:
    SELECT * FROM Server.Catalog.Schema.Table

    Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'

    Thanks!
    Josh

  2. #2
    Join Date
    May 2002
    Posts
    299
    Try creating a new linkedserver to FP and see if it works. Also, try to update to the latest Mdac to get the latest providers.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Jan 2005
    Posts
    27
    Yeah, I already tried to create a new linked server, and it didn't work. I checked my MDAC version, and it's the newest one (2.8 SP1).

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what foxpro driver are you using? a long time ago in a galaxy far away I had a world of trouble with fox pro linked servers until I upgraded from the 7 driver to the 8 driver.

    I would drop the linked server.

    install the current VFP driver. maybe reinstall MDAC.

    recreate said linked server.

    test the connection with OPENQUERY (but do not use it in production, pushes the processing to foxpro).

    test using your empty dot method.

    so glad I do not have to use fox pro anymore.
    “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.

  5. #5
    Join Date
    Jan 2005
    Posts
    27
    It looks like I'm running version 6... I'll try upgrading it, thanks.

    Oh yeah, I've already tried the OPENQUERY method, and it works. I could just change all of my code to use that, but that would be a lot of work.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    OPENQUERY is a pig. It will process the query in foxpro instead of sql server.
    “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.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what version of VFP are using. Be careful. Try this out in a test envrionment first because you may not be able to go back.
    “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.

  8. #8
    Join Date
    Jan 2005
    Posts
    27
    Ok, so I wouldn't want to use it, even if it was easy to use.

  9. #9
    Join Date
    Jan 2005
    Posts
    27
    I actually don't know what version it is. We have a third-party application written in FoxPro that we pull data from to display on an intranet site. My FoxPro experience is limited to using that app, and linking SQL Server to it.

    So, thanks for the warning. I'll have to do more research...

  10. #10
    Join Date
    Jan 2005
    Posts
    27
    Ok, I got it. It turns out that there is a checkbox in the linked server's Provider Options window called "Level zero only". If that is not checked, the empty-dot notation doesn't work. I checked it, and it works now.

    I'm not sure why it broke, unless that option is a recent addition to the provider options - maybe someone installed some update around the same time we reinstalled SQL Server...

    Anyway, thanks for the help!
    Josh

Posting Permissions

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