MSSQL 2000 -> 2005 - Schema stuff?

Hi All,

I imported a database backup with no problems.
I can view the data using the Studio.
However, I noticed that all the tables are now pre-appended with a Schema name.
This also applies to Views.

How do I get 'rid' of this as my application doesn't know about the Schema and claims that it can't find the Tables and Objects I'm referenceing in the web.config.

Thanks,

-Alon

Hi,

schemas were introduced in SQL Server 2005. if the object is no in the default schema of the user, SQL Server will search for it in the dbo namespace. So, there are several options for you to make it work:

-Change default schema of the user which accesses the database to the schema the tables were imported.
-Directly import the tables to the dbo schema / or move them to the appropiate schema.
-Prefix your object in the queries with the schema name
-Schema are quite useful, you should get familiar with them, a lot to read is in the BOL. You should also consider using prefixes in your queries per se.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

While schemas are new in SQL 2005, SQL 2000 had the same basic idea (at least as far as calling the objects). In SQL 2000 objects had owners, and these were prefixed infront of the table name. In most cases dbo was the owner.

Take the application account that is logging into the database. Change it's default schema to the schema that the objects are in (probably the dbo schema) this will fix the problem. By default when you upgrade a database all users are setup with there own schema as there default schema.

You may want to remove any unneeded schemas from the database so that objects don't get created in the wrong schema.