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.
- 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.
- 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é.
Thanks,
man, you save me… thank a lot.-
Thank you! This solved my problem completely!
This tip was helpful especially with having the linked server to work with a Excel spreadsheet through a ODBC DSN (MSDASQL).
Thanks!
Tnanks a lot.. you really help me with my problem.. !!
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!!
Thanks. It works in our shop.
Thanks for the tip. This saved me a lot of time.
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.
I’m not sure John. I have very little actual understanding of the problem despite trying to decipher books online.
Thank you guys . It works great.
Thanks a lot. I was looking for this.