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é.
Leave a Reply