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é.
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.
Smoked sausage
As the alarm screeches and my extremities freeze from the fans trying to propel smoke through the wide open doors and windows I’m realizing… I don’t have a clue how to cook sausage.
Blame my vegetarian upbringing.
Chicken Speed
Today on A Prarie Home Companion re-broadcast I heard Molly Ivins tell a story about Austin Texas in the 60’s that ended with a chicken on speed. I can’t tell you how much that meant to me but I would like you to imagine a chicken on speed and tell me you don’t get tingly inside.
The fact that I’ve referenced a single “recreational” drug in 100% of my posts this year shouldn’t concern you at all.
Stay in school.
Writing Star
I’ve been hooked on the internet since my first page load in some unremembered location at some unremembered time (definitely pre-Google) . In those days the web was wild with roaming herds of gifs and copyright infringement and I had some of the most tasteful gifs and infringement in my entire Yahoo chat crew.
Years passed and the more the web grew, the more I loved creating things to put on it.
When I finally plunked down the cash for a domain name and hosting space, I was full of bright ideas and high expectations. I was going to write witty articles, helpful tutorials, insightful reviews and other such highbrow content. I was going to join The Web Community(tm) and be the next Molly, Scrivs or Dooce. The only thing standing in the way of my internet stardom and the subsequent groupies was the fact that the current (and then) Sunshine is terminally lacking in ambition.
The bruised, protesting web-rock-star inside of me cries little (tasteful) tears of agony every time I play games instead of writing or visit jayisgames instead of alistapart. I’m generally fine with that. Jay rocks and I’m working on a completing all missions in Kingdom Hearts II. I’m also busy collecting hobo gristle in Kingdom of Loathing. That takes time.
Also (in case you hadn’t noticed) I’m not writer of words. (Or a speaker of words but that’s another story). I’m primarily a reader of words. Most things I’ve ever written were brilliant in the heat of composition, but stank like hot fish guts two days later. In fact I stopped reading anything I wrote because the cringing was affecting my posture.
But, surprise, surprise, some of what I’ve written here does not make me want to hide under a rock. I was tagging old posts with the new and improved Wordpress tagging feature and felt really weird… turns out ir was the feeling of not blushing with shame. Yes the spelling, proofing and general sentence structure could use some help (really… was anybody going to tell me that I always spell friends wrong?) but the actual writing is bearable. Some of it even makes me laugh.
And that’s almost as good as being a webstar because groupies…? They are more trouble than they’re worth. So I hear. But words you’re not ashamed to re-print in the Holiday newsletter? That’s worth a gold star.
So this year we’re (I’m) going to try for a regular posting schedule so that I can perfect this phenomenon, or at least crank out some more goodies before the magic* leaves. It might be once a week or it might be once a quarter but it will be. Your mission, should you choose to accept it, is to act properly shocked and welcoming towards this post (yes this one that you’re reading now) and to anticipate future posts with all of the (tasteful) fervor of a chihuahua on speed.
*Your thoughts, opinions and/or revelations on the quality of previous entries on this site are irrelevant to them being magic. Just so you know.
Happy New Year
Entries