Author: Sunshine Lewis

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é.

Crunchy Buttered Squares of Love

Most of us have used random nouns as terms of endearment, (honey, peanut, cupcake). It seems like a common human trait to label the ones we love with various words that aren’t necessarily descriptive or meaningful.

Take pumpkin. It’s a large rounded food in the squash family that tends to be a rather lovely shade of orange. It has a mild taste which is best described as “not bad”. It is commonly carved or displayed as fall decoration. When it is eaten, it is cooked beyond recognition, disguised with cream, butter and strong spices and served up in a pie shell, preferably with whipped topping or ice cream so that no one will suspect it of being a reasonably healthy vegetable.

Pumpkin, as a term of endearment makes me think of an orange bulbous object that doesn’t taste good unless it’s mixed with other things. Something that makes a splash on concrete. Yet people who have never even tried pumpkin still accept it as a term of endearment.

I imagine that someone a long time ago (Peter Pumpkin Eater maybe) that thought pumpkins were great. So great that he started calling his wife and kids “pumpkins” so that he could be cast in Real World Mother Goose as the “wacky” one.

It makes you wonder right?

I’m going to start calling my nephew my little buttered toast. LBT for short.