October 18, 2010 at 9:46 pm
Hi All,
Our Scenario is we can upgrade from sql 2000 to 2008 server.
I restore sqlserver 2000 db's to sqlserver 2008 express edition client tools.Then i change the
compatablilty mode.
I used to select * from sysobjects query..
Some of the objects comes under dbo schema like Sample Windows credentials and some of the objects comes under balaji schema..
dbo schema is associated with sample login and sample user. when i ran the select query using dbo schema objects. Its runs successfully.
In Sql 2000
login : sample
Password : sample
then i ran the select * from samples, its runs successfully
In sql 2000
login : test
password test
i want to run the sample table here. This table is associted with sample schema and sample login
select * from [sample].[samples]
then its run successfully
But in 2008
login : sample
password : sample
after login the sample name. then i tried to exeute the samples table here
select * from samples
its shows Invalid object name.
then i tried
select * from [sample].[samples]
afterwards its been executed,
An exisiting server (sql 2000). its runs successfully. with out using the schema name
but sql 2008. i cannot able to run the select query with the sample login name..
Can you pls advise me.. where is the problem occurs.. how can i fix it?
Thanks
Balaji.G
Regards
Balaji.G
October 19, 2010 at 7:44 am
maybe you should change the ownership back to dbo.
EXEC sp_changeobjectowner 'TableName', 'dbo'
October 19, 2010 at 8:01 am
Maybe you need to set the default schema of the user - the user seems to be mapped to the "dbo" schema after your upgrade?
From this link:
A query such as "select * from table1" in SQL Server 2000 goes through a set of steps to resolve and validate the object references before execution. The search first looks at the identity of the connection executing the query. Let’s assume we have a user connection through "user1". For “select * from table1”, SQL Server 2000 searches for the object "user1.table1" first. If object does not exist, the next step is to search for "dbo.table1".
...
Reusing the example from above; with DEFAULT_SCHEMA set to 'dbo' in a SQL Server 2005 database, user1 executing 'select * from table1' will directly resolve immediately to 'dbo.table1' without searching for 'user1.table1'.
[edit]:
Maybe this isn't the reason. From the same article:
In fact, if you use the new CREATE USER syntax from SQL server 2005, you will get ‘dbo’ as the DEFAULT_SCHEMA by default. So if you are developing in a new database under SQL Server 2005, you are already taking advantage of this name resolution shortcut. However, the behavior for upgraded databases is different . During upgrade from previous versions of SQL Server, to preserve the same behavior, SQL Server 2005 sets the DEFAULT_SCHEMA to the name of the user. Example: ‘user1’ gets DEFAULT_SCHEMA of 'user1' when you upgrade a database from an earlier version to SQL Server 2005 by attaching or any other method.
The upgrade should have ideally taken care of this and it looks like you shouldn't be facing this issue. Looks like what Geoff recommended is more valid.
October 21, 2010 at 7:26 am
Hi Winash,
Thanks for your reply...
As per Geoff instructions, if i changed the objects owner from user schema to dbo schema
its working fine. but an existing server runs at succcessfully using user schema.
Say for example :
in sql 2000:
database : sample
user login : balaji, password jrdba#21
when i used this credentials in sql 2000 server. its ran all objects successfully
and if i used some other credentials.. i need to be specified at
use sample
select * from [balaji].[tablename] like this
i had apply the same procedure in sql 2008.
database : sample
user login : balaji.
password : jrdba#21
if i ran any objects using select query.. it will not run..
Error is " Invalid object Name"...
But in sql 2000. it will work.
I cannot change the owner of a tables. because an application will call the db objects like this
userschema.objectname..
Can you pls help me
Regards
Balaji.G
October 21, 2010 at 1:04 pm
In your new SQL 2008 environment - did you try changing the default schema of your user login and see if that helps?
Something like this:
ALTER USER balaji WITH DEFAULT_SCHEMA=balaji
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply