DTS between SQL server 2000 and Oracle 8.1.7

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.

Does anyone have same experience? Could someone please give me suggestion?

Thanks in advance!Before you started the DTS transfer, did you define the table in the tablespace you want it to be? If you just let DTS create the table, chances are it's in System.|||Originally posted by joejcheng
Before you started the DTS transfer, did you define the table in the tablespace you want it to be? If you just let DTS create the table, chances are it's in System.

I defined the oracle schema before I started DTS transfer. The tables are in right schema and right tablespace. I also tried to drop those table, but I can't drop in oracle.

Thanks,

Lucy|||Originally posted by llin
I defined the oracle schema before I started DTS transfer. The tables are in right schema and right tablespace. I also tried to drop those table, but I can't drop in oracle.

Thanks,

Lucy

I found I have to put double quote around table to select data in oracle.

select * from "friends";

Lucy|||The table names in Oracle are case sensitive.

This may be your problem. Try the statement in UPPER case and in camelCase.

Good luck

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tells
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this. I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks.
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tells
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this. I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks."Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - transform SQL to ORACLE

I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
process runs successfully, however when I go to ORacle to select it, it tell
s
me the object does not exist. The dts process drops and recreates a table
everytime I run it from SQL to Oracle. No errors during the whole thing.I'm
not having any luck finding a log file or trace file to trouble shoot this.
I
can see the table in Oracle. I can run dba_objects command and it shows that
(created) table as existing. However I cannot describe it or select it.Any
ideas? Thanks."Leida" <Leida@.discussions.microsoft.com> wrote in message
news:5EB72B50-5EC1-47FC-875C-ED43B0B6D316@.microsoft.com...
>I am trying to DTS a SQL table to ORacle using MS ODBS for Oracle. The
> process runs successfully, however when I go to ORacle to select it, it
> tells
> me the object does not exist. The dts process drops and recreates a table
> everytime I run it from SQL to Oracle. No errors during the whole
> thing.I'm
> not having any luck finding a log file or trace file to trouble shoot
> this. I
> can see the table in Oracle. I can run dba_objects command and it shows
> that
> (created) table as existing. However I cannot describe it or select it.Any
> ideas? Thanks.
What shema is the object in? If it's not in the current schema for your
connection, then you must use a schema-qualified name to access it.
David

DTS - problem on after inserting .csv file into sql database

Hello,

I inserted a .csv file into the database using DTS and it get inserted successfully.

But my problem is, the csv file contains the datas that has double quotes on it (eg: 1/2" iron rod, 3/4" wooden rod).

When I checked the inserted datas, it was inserted as "1/2" iron rod, 3/4"""," 3/4" wooden rod"".

How do I eliminate these additional double quotes.

Raviraj Danasekaran

The pattern of what it did (per your example) doesn't make sense to me.

That said, one way would be to update the affected columns using a replace function.

update my_table

set my_problem_column = replace(my_problem_column,'""','"')

(that's a single quote followed by two double quotes followed by a single quote, then a comma, then a single quote followed by a double quote and a single quote.)

That's a brute force method, but it might work to fix the problem if it's a one-time thing.

You might need to adjust your dts packages, there might be some values you can set that would change the quote handling behavior.