November 1, 2010 at 1:55 pm
I inherited some VB code that used the ADOX.Catalog (Microsoft ADO Ext. 2.8 for DDL and Security) to check how many columns should be in a given master named "Routes" table compared to a temp table. Now we are upgrading the clients to SQL 2005 and 2008 and were getting errors because the Routes table only has 56 columns, but the catalog was returning 64 in the column count.
This is because SQL 2005 and above now have a sys View named "routes".
select obj.name, s.name as schema_name, type_desc, is_ms_shipped
from sys.all_objects obj left outer join sys.schemas s on obj.schema_id = s.schema_id
where obj.name like 'Routes'
returned:
name schema_name type_desc is_ms_shipped
------------ ----------- --------------- -------------
Routes dbo USER_TABLE 0
routes sys VIEW 1
Thanks MS!!
This appears to be the only User Table that has this issue for me:
select * from sys.all_objects where type='U' and name in (select name from sys.all_objects where type <> 'U')
only returns my Routes table.
g
November 1, 2010 at 2:12 pm
There is a reason why it is recommended to schema (owner on 2000) qualify table names. Explicitly naming it dbo.routes would not cause a problem.
This shouldn't cause a problem at all unless all your users somehow got mapped with a default schema of sys. Name resolution is default schema first, db schema second. The default resolution for a non-qualified table should never be sys.
On my 2008 server
select * from routes
Msg 208, Level 16, State 1, Line 1
Invalid object name 'routes'.
To get sys.routes, I have to explicitly query sys.routes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2010 at 11:17 am
True. When you use one of the multitude of ways to check the existence of a table such as your "select * from routes" sql.
However:
The ADOX.Catalog checks the system objects records "catalog" for the columns, etc., for a given table name.
It was just a surprise that MS would create a view or any other object that was not more precisely named e.g., "sys_routes", or "net_routes".
December 10, 2010 at 12:50 pm
Glen Potter (12/10/2010)
TIt was just a surprise that MS would create a view or any other object that was not more precisely named e.g., "sys_routes", or "net_routes".
sys.objects
sys.indexes
sys.partitions
It's a system table (more correctly view) that contains routes. Hence sys.routes.
I'm surprised that an ADO driver doesn't understand the concept of schemas, (and pre SQL 2005 owners). It's perfectly valid to have two tables with the same name in two different schemas, but as soon as you do that, the ADO driver in question will break. Pre SQL 2005 is was possible to have two tables, same name, different owners. Same problem with the driver.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 8:11 am
Thanks for the responses... as stated I already have a "fix", the intent was as a "heads up".
I don't think adding a sys table/view with such basic names should be expected. How many issues will arise (with legacy dbs) if a table/view named "items" were to be introduced?
Again, not looking for any solutions ... just warning people that these exist.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply