Migrate SQL 2000, cant query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Migrate SQL 2000, cant query

Author

Topic

edgarsk
Starting Member

8 Posts

Posted - 2010-04-22 : 09:46:01

Hello,
I need help to resolve this problem,
Source:
Windows SQL server 2000 SP4
I made full DB backup of my DB (TEST_DB)

Destination:
windows SQL server 2005 SP2
I restore DB (TEST_DB)

But now i cannot make query like:
select * from table1
I only can select like this:
select * from user.table1

In SQL 2000 all works fine.

Any have ideas what is problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 09:48:43

that means your default schema is not user. change the default schema to user if you want to continue refering tables without prefixing schema name


ALTER USER <your username here> WITH DEFAULT_SCHEMA = user;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edgarsk
Starting Member

8 Posts

Posted - 2010-04-22 : 12:08:07

quote:


ALTER USER <your username here> WITH DEFAULT_SCHEMA = user;

I done this, but doesnot work..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 02:37:07

why? whats the issue? what does below give you?

SELECT SCHEMA_NAME();

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edgarsk
Starting Member

8 Posts

Posted - 2010-04-23 : 04:37:45

quote:
Originally posted by visakh16

why? whats the issue? what does below give you?

SELECT SCHEMA_NAME();

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I dont know why i can't select....
give new query tab

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:13:45

what? what does it return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edgarsk
Starting Member

8 Posts

Posted - 2010-04-23 : 11:49:13

quote:
Originally posted by visakh16

what? what does it return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nothing

edgarsk
Starting Member

8 Posts

Posted - 2010-04-26 : 05:16:18

When i create Synonym, like:
synonym name: TABLE1 (previous user.TABLE1)
synonym schema dbo
server name...
Schema epas
Object name TABLE1

after this i can make queryy from table TABLE1 without user prefix user.TABLE1

edgarsk
Starting Member

8 Posts

Posted - 2010-04-26 : 10:03:59

quote:
Originally posted by visakh16

what? what does it return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

now he return schema dbo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 00:48:35

quote:
Originally posted by edgarsk

quote:
Originally posted by visakh16

what? what does it return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

now he return schema dbo

you mean after you set synonym?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edgarsk
Starting Member

8 Posts

Posted - 2010-04-27 : 01:27:52

quote:
Originally posted by visakh16

quote:
Originally posted by edgarsk

quote:
Originally posted by visakh16

what? what does it return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

now he return schema dbo

you mean after you set synonym?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

when do:
SELECT SCHEMA_NAME();

edgarsk
Starting Member

8 Posts

Posted - 2010-04-27 : 09:24:43

Maybe any ideas what can i do anymore, to resolve my problem about it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources