An invalid schema or catalog was specified for the provider “MSDASQL” for linked server

Make sure “level zero only” is checked on the MSDASQL provider.

Now to the question!

SQL Server’s linked server feature lets you link to a remote server through an ODBC connection. Once the server is linked, you are supposed to be able to reference it using four-part notation like so:

SELECT * FROM linkedserver...tablename

If you’re like me that query will give you an error message instead of a result set. So you will Google the error and be told by forum posts the world over that the openquery function is your only hope.

Now openquery is a great little piece of work but it doesn’t contribute much to code-readability. But since it’s your only hope, you’ll try to clean things up by creating elaborate views that mimic the tables in the linked server and then using those views in other views until the whole mess is so complicated that all of your queries timeout and you spend most of your database time watching a progress bar and wondering why life hates you.

Here’s the alternative.

  1. Go to the provider options screenIn SQL Server 2005 you can see the list of providers in a folder above the linked server (assuming you have appropriate permissions). Right click on MSDASQL and go to properties. In SQL Server 2000, the provider options button is in the dialog box where you create the linked server.
  2. Check the box that says “level zero only”

I don’t know what this means (even after reading the definition). I think it means that the SQL Server won’t do any optimization of the result set (it will be the responsibility of the linked server) but that’s just a guess.

I do know that after checking that box, my four part query worked. It took me about three days of searching to find that answer at the bottom of a thread somewhere so I’m posting it here in hopes that the Google gods will smile upon it.

Also thank you Alexandre Léveillé.

59 thoughts on “An invalid schema or catalog was specified for the provider “MSDASQL” for linked server”

  5. for others also researching this issue. you will come across an alternative notation like this: select * from LINKEDSERVER.DSNNAME..TABLENAME.

    this seemed to work, but wound up throwing an access denied error from my remote server. not sure why as i can use the dsn from within a dts package without incident. i was not able to diagnose that and turned on the level zero switch and it works now. thank you!!

    Reply

  8. Worked for me. Thanks for the tip. But I wonder.

    I could change “level zero” back to 0 (off) and it would still work while on the server. Could it just be initializing MSDASQL on the server?

    Testing from my PC, it didn’t work set to 0 from the client, but changing “level zero” again back to 1 fixed that problem as well.

    Reply
  21. Pingback: JIRA: Academic

  32. After googling ALL DAY I finally found this solution. Thank you, these next lines are to elevate visibility. For sql server 2008 to use a mysql linked server without openquery, an openquery alternative is to enable Level Zero Only on the provider properties.

    Reply

  34. This works just fine, for further explanation the “Level Zero Only” restricts access to the linked server DB especified on creation.
    You can’t switch to others DB’s on the Linked Server.

    The sintax LinkedServer…TableName will work, the LinkedServer.DataBase..TableName sintax wil throw error.

    Reply

  41. OMG! THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    I’ve been pulling my hair out over this issue.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *