April 2, 2004 at 6:38 am
I have never seen this before, and I am hoping that someone may be able to shed light on this:
The app copies an existing mdf/ldf set onto a new server (7 in this case) and attaches the files. We run everything needed to clean up foreign users/logins/etc. At some point in time, we execute this:
SELECT table_schema + N'.'+ table_name as lfName from information_schema.tables where TABLE_TYPE = 'BASE TABLE'
and instead of getting back what you would normally expect, I get
Invalid object name 'information_schema.tables'.
Every other table in the db will respond properly, and model contains the 'information_schema' views. Any suggestions as to what I might be missing here?
April 2, 2004 at 6:57 am
information_schema.tables is a view in master (not sure in sql7).
Check on sp_helptext [information_schema.tables] to find it out.
April 2, 2004 at 7:10 am
The SQL Server isn't set to case-sensitive, is it?
K. Brian Kelley
@kbriankelley
April 2, 2004 at 7:34 am
No, it isn't, but that was a good thought - this works on every other db I have - I'm beginning to think this is an anomoly, but if anyone else has seen this, I would like to explore it further...
April 2, 2004 at 7:40 am
wz700 - Thanks for the hint - I followed up on your suggestion: Check on sp_helptext [information_schema.tables] to find it out.
As with my direct select attempt, it works fine on every other db except my problem child...
Also, it appears as if this view (as well as the other IS views) is in every db, which is why I checked model first. The only issue I know of with sql 7 is that the INFORMATION_SCHEMA.ROUTINES is not present in 7 databases.
April 2, 2004 at 7:55 am
Check on your source db (where mdf/ldf from).
If necessary, add these views (it should have if created from msdb).
April 2, 2004 at 10:04 am
Does the instance have a case sensitive default collation even though your database doesn't? I run Latin1_General_Bin as my default collation and the above query needs to be changed to the follwing in order to work.
SELECT TABLE_SCHEMA + N'.'+ TABLE_NAME as lfName from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply