jump to navigation

INFORMATION_SCHEMA is not a valid object in AdvWorks..!? May 28, 2009

Posted by msrviking in General.
trackback

Sometime today, I wanted to have AdventureWorks database on my new workstation SQL Server instance. So I went ahead and download AdventureWorks database installable from here and I don’t have to talk about this website. I quickly scanned through the bunch of files that were available and found what I wanted. Well, what next – downloaded 27M file and installed it, and queried my favorite query against the new database “select * from information_schema.tables”.

Woooo, I got this error and kept getting this error

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘information_schema.tables’.

My, my what am I into, and I was confused for few moments but didn’t panic at least :). So as usual I got into digging mode to find out what is it all about. Here are the few top reasons I could think and went finding out serially (although not a big deal reasons, but these matter a lot).

– Is this object really there in the database? Yes, verified through the object explorer.

– Is the object name correct, and the schema name valid? Yes, verified through the object explorer, and common sense along with years of  experience says that this metadata view is existing.

Then why on the earth I am not able to access? Something put me thinking about the case sensitivity of the object name and yes my thinking was correct. I immediately changed the query to “select * from INFORMATION_SCHEMA.TABLES”. Viola, it works great but I thought how many queries should I keep writing to address case-sensitivity problem.  Now back to fixing mode, and the options I have are

– Write the queries which will have case sensitive object names. Not possible, and it will make me crazy.

– Change the collation of the database from case-sensitive to case-insensitive. Well not possible because there are several objects (tables, indexes,….) in the database that are dependent on this collation. If I have to change the collation then there should be changes in each object.

Oh, but, how did it happen that I have got a sample database installable which is case-sensitive  in the first place? Did I miss something while I chose the file that needs to be downloaded?

Answers to my pondering questions were straight. Yes I downloaded the installable of the case-sensitive database and also I realized I can’t change the collation too. Due to time factor and since I was running out of patience I had to drop the “wrong” database, and I had to install the “right” database in my system. Well I am happy now that I have right database up and running for me to dig on something new which I shall blog soon.

Learning lesson – look for the right database installable before getting into a “waste-of-time” mess.

Hope my experiences gives you guys an idea how my life is going on with so many (?) things happening around :).

Cheers and enjoy your happenings too!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: