Hi,

I used DTS Export Wizard to copy tables from SQL server 2000 to Oracle
8.1.7. After transfering completed successfully, I loged into oracle and
tried to select data from the tables. I got 'table does not exist' error.
But when I select table_name from oracle data dictionary, I saw tables exist
in oracle. Then I opened SQL Query Analyzer, I can select data from the
tables just transfered through linked oracle server.


I defined oracle schema before I started DTS transfer.
Does anyone have same experience? Could someone please give me suggestion?

Thanks in advance!

Lucy

Case sensitive?
Wrong Schema?

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lucy Lin" <llin (AT) advance (DOT) net> wrote

It is in the right schema. Following is the output from oracle database:

SQL> show user
user is "DEMO_WEB"
SQL> select OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS from user_objects
where object_name like 'f%';

OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ --------------- --------- -------
friends TABLE 10-FEB-04 VALID

SQL> desc friends
Object does not exist.
SQL> select * from friends;
select * from friends
*
ERROR at line 1:
ORA-00942: table or view does not exist

But I can select data through oracle linked server by using SQL Query
Alalyzer.

select * from OrclDB..DEMO_WEB.friends

Thanks,
Lucy
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

It does look strange

what about on Oracle

select * from DEMO_WEB.friends;

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lucy Lin" <llin (AT) advance (DOT) net> wrote

SQL> select * from DEMO_WEB.friends;
select * from DEMO_WEB.friends
*
ERROR at line 1:
ORA-00942: table or view does not exist

I tried to drop table, but I can't drop it because Ora-00942 error.

I don't know whether whether I can drop it through oracle linked server. I
tried following and it didn't work.

drop table OrclDB..DEMO_WEB.friends

Thanks,

Lucy
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You are right. It is because of case. I have to put quote in select
statement.

SQL> select * from "friends";

FRIEND_ID FRIEND_NAME
---------- -----------------------------------------------------------------
---------------
1 Jing Gao
2 Jie Wu
3 Brian Pan
4 Peter Xu
1 Jing Gao
2 Jie Wu
3 Brian Pan
4 Peter Xu

8 rows selected.

Thanks,

Lucy

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote