Cannot insert duplicate key row in object 'dummyTable' with unique index 'dummyIndex'.

Question

I am currently migrating data from Sybase to SQL server 2005. I have clustered index in Sybase. When I migrate data,with this index(migrate schema) below is result in output window in SSMA

-------------------------------------------------------------------------------------------------------
Migrating data...
Analyzing metadata...
Preparing table Prod002.dbo.dummyTable...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table 'Prod002.dbo.dummyTable': > NIC.dbo.dummyTable, 234 rows total
Cannot insert duplicate key row in object 'dbo.dummyTable' with unique index 'dummyIndex'.
Errors: Cannot insert duplicate key row in object 'dbo.dummyTable' with unique index 'dummyIndex'.
Completing migration of table Prod002.dbo.dummyTable...
Migration complete for table 'Prod002.dbo.dummyTable': > NIC.dbo.dummyTable, 0 rows migrated (Elapsed Time = 00:00:00:00:688).
Data migration operation has finished.
0 table(s) successfully migrated. 
0 table(s) partially migrated. 
1 table(s) failed to migrate.
-------------------------------------------------------------------------------------------------
If I remove unique index 'dummyIndex', after migrate data the very first record in my first column in table is duplicating to all others records. How to solve this problem? See below for duplicate data.


Urgent help needed.
Thanks,

---

ckp

Answer 1

Hi CHIRAG,

Any progress?

Refer to Migrating from Sybase ASE to SQL Server – How to use SSMA for Sybase: http://blogs.msdn.com/b/ssma/archive/2011/06/09/migrating-from-sybase-ase-to-sql-server-how-to-use-ssma-for-sybase.aspx.

Thanks.

---

Maggie Luo
TechNet Community Support

Answer 2

@Maggie: I already went through this link. It is very old solution approach. I am not sure whether SSMA 5.2 supports Sybase 12.5 or not to migrate to SQL server 2005.

If there is any another approach let me know.

Thanks,

---

ckp

Answer 3

You showed us the problem data on the SQL Server side of the transfer.  Can you show us the same rows as they appear on Sybase before being transferred?

I do not use SSMA, but perhaps a look would give a clue.

RLF

Answer 4

@Russell: See above the source data in Sybase.

By the way, what others solution approach you preferred other than SSMA?

---

ckp

Answer 5

ckp - First, I do not use SSMA since I do not have much in the way of other brand databases to import. 

Even when I do, I use something like BCP.EXE (which exists in SyBase) to export to a flat file, then something like BCP.EXE (which also exists in SQL Server) to import the flat file.

If I can connect without too much trouble, I might use the SSMS Import/Export wizard.

Now, about your unique index and the table above.   I wonder what columns are intended to be included in the unique index.  Just looking at the first two rows of the source data tells me that the Sector & Category columns are not unique and therefore a Unique Index across those two columns will cause failures. 

In the Sybase screenshot, I see a column of numbers to the left.  Is this data from the Sybase database or just a column of row numbers next to the data?  If it is data, then you are apparently not transferring that data into the new database.  I suspect the first two repeating columns may be an artifact of the SSMA having problems.  (But I do not know that, of course.)

For help, I suggest (since the code is so similar to SQL Server) that you script out from the Sybase server the table definition, including constraints and indexes.   Then post those here for examination.   If the SSMA has created tables and indexes on your SQL Server, script out the SQL Server table definition, including constraints and indexes.

You can compare them yourself to see what is different, but feel free to post them here if necessary.

All the best,
RLF

Answer 6

@Russell: I have done successfully with BCP utility. But not sure whether the float datatype have consistency in data when it migrated from Sybase to SQL server 2005?

CREATE TABLE 
[domstats]
(
   [sector] char(18) NOT NULL,
   [category] char(18) NOT NULL,
   [date] datetime NOT NULL,
   [short] float(48) NULL,
   [mid] float(48) NULL,
   [long] float(48) NULL,
   [universe] float(48) NULL
)

CREATE UNIQUE CLUSTERED INDEX xdomsta1 ON domstats

(
   [sector] ASC,
   [category] ASC,
   [date] ASC
)

If Unique index doesn't allow to insert new rows into table. then I wondered why BCP allowed me to insert all 234 rows into SQL domstats table even-tough there is Unique index(see above script for index and table). Why Unique Index across those two columns were not cause any problem using bcp (SQL version). I have only trouble when i am doing this steps in SSMA. 

If anyone who is master in SSMA or used and faced already this type of behavior those people can help here. Hope someone definitely will come with great help.

Thanks,

---

ckp

Answer 7

First, congratulations on getting the BCP to work.

Second, I suspect that the problem is in SSMA, either with its configuration or else a bug has been exposed.

Regarding the float precision, of course implementations differ.   You can examine the values manually since there are only 234 rows.  You also might try summing the rows together and see is there is significant difference in the Sybase SUM and the SQL Server SUM.

SELECT SUM(Short) AS ShortSum,
   SUM(Mid) AS MidSum,
   SUM(Long) AS LongSum,
   SUM(univers) AS UniverseSum
FROM dbo.domstats;

Then you could check the two sets of SUMs.

I hope someone more SSMA aware can help you with that problem.

RLF

Answer 8

It seems like Sybase Open client driver issue. I need Sybase ASE OLE DB provider for 12.5. Is anybody has link or source to download it?

---

ckp