DTS fails coes it refuses to fill the unique id colum

THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column.

"Cannot insert the value NULL into column UID column does not allow"

Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed.

Is this common?

THe source and destination tables are in the same databes.

When you click on "Transform" uncheck where it says "Enable Identity Insert"

|||

Thank you very much.

It worked.

I am not sure I "logically" understand that though.

|||

When you have enable Identity Insert checked in your DTS wizard what it does is says "I want you to insert the identity field that is coming from the table I'm copying from". This is useful when you have a truncated table and you want to keep your identity field values for data integrity.

Now comes the however.

When you set to Ignore the Identity column in your transformation you were essentially telling it to insert null values, and Identity columns can't be null. Had you not selected that then you would have most likely received the error that says you can't insert duplicates into your identiy column as it violates the unique constraint.

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

I am transfering data from a production server to a test
sever. I started a transfer with the DTS wizard then I
realized that I had the name spelled wrong in the to part
so I removed that db from the drop down list. Now when I
try to do another transfer I get the following error
message.
Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
it alredy exists.
When I check the test server this file does exist.
What do I need to do to get my transfer to go?
Can I just delete the above mentioned file?
What is the purpose of the data folder?What are you talking about?
I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
Whats the to part?
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
Then dont try to re-create it.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
Of course you can. But keep in mind this is a db file. It
sounds though as you just created it so it's probably
empty. Drop the db from Enterprise Manager.
Why are you exporting to a file anyways?
>--Original Message--
>I am transfering data from a production server to a test
>sever. I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
>so I removed that db from the drop down list. Now when I
>try to do another transfer I get the following error
>message.
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
>.
>|||The to part that I was refering to is on the Chose Destination page of the DTS wizard in the database drop down list. In that list it has a new option. After selecting that I typed in the name of the database that I want to create with the data transfer. I am not trying to create the file that gives me the error I assume that this is something that is created by the DTS.