Unicode migration - R3load import error - Violation of PRIMARY KEY constraint 'ATAB~0'

[Former Member]

My company plans on migrating from Oracle/Windows to SQL Server/Windows and decided to add a Unicode conversion since we are doing a db export/import.  The migration to SQL Server had no errors however when I repeat adding in the Unicode migration, on the import of ATAB, I get the following error:

(IMP) INFO: ExeFastLoad failed with <2: BCP Commit failed:Violation of PRIMARY KEY constraint 'ATAB~0'. Cannot insert duplicate key in object 'sbx.ATAB'. The duplicate>

(IMP) ERROR: ExeFastload: rc = 2

I checked the source Oracle database and there are no duplicates in ATAB and if there were, I would think this would have been an issue going to SQL server in the first round.  I then tried switching switching off the fastload option, using dbsl but get the following error:

(IMP) ERROR: DbSlEndModify failed

  rc = 26, table "ATAB"

I have followed all of the pre-steps in the Unicode Conversion Guide, I am using the latest R3load, R3ldctl, R3szchk database library and I checked the UMGPOCNV table and it is populated with 6 entries, one for ATAB.  I have attached several related files, the SAPPOOL.log file shows the actual error.  Any help would be appreciated.

Thanks,

Doug

[former_member189725]

The only way to get out of it is to export the ATAB table again separately using R3load .

I believe the source and target database would have the same primary key .During the export , was there an error in between and export was restarted ?

Regards

Ratnajit

[Former Member]

The export completed successfully but I did find this in the log file:

rscpMCConvertM: moLangSource is strange ! !

(EXP) TABLE: "ATAB" #20120519001006

I don't see why a repeat export of the ATAB table would yield any different results.

Thanks,

Doug

[former_member189725]

Can you please go through this SCN thread.

http://scn.sap.com/thread/2128014

and see if its useful.

Regards

Ratnajit

[Former Member]

I checked that thread but the error on export looks different than what I am seeing, also the source and target system primary key fields have the same length and are the first two fields per the below:

Source ATAB:

TABNAME:CHAR 10

VARKEY: CHAR 50

DATALN: INT2 5

VARDATA: RAW 452

Target ATAB:

TABNAME: NVARCHAR 10

VARKEY: NVARCHAR 50

DATALN: SMALLINT 2

VARDATA: VARBINARY 904

Thanks,

Doug

[nils_buerckel]

Hi Doug,

did you check and compare ATAB entries in both source and target ?

In fact it is possible that the Unicode conversion causes duplicate keys:

If key fields contain Non-supported characters in the source code page, the conversion maps these to "#".

If this happens multiple times, then duplicate keys can occur.

Best regards,

Nils Buerckel

[Former Member]

Hi Nils,

I do see 64 entries in the target system where the VARKEY field contains one or more "#" characters but these are also in the source system.  The number of entries in the source system is 1,709,092 but in the target system is 470,120 so I cannot actually see the duplicate entry since it cannot be loaded into the table.  I did find that I was using different versions of the R3 tools on export versus import, could this be the problem?

Regards,

Doug

[Former Member]

The import was done with a higher version of the R3 tools but when I matched the version I still got the same error.  I need to be able to allow the duplicate rows to be loaded so I can see what they are.  I tried changing the index using the following but it still shows as a unique index  when I check it and sapinst fails at the same point:

GO

/****** Object:  Index [ATAB~0]    Script Date: 05/25/2012 10:45:35 ******/

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[sbx].[ATAB]') AND name = N'ATAB~0')

ALTER TABLE [sbx].[ATAB] DROP CONSTRAINT [ATAB~0]

GO

USE [SBX]

GO

/****** Object:  Index [ATAB~0]    Script Date: 05/25/2012 10:45:36 ******/

ALTER TABLE [sbx].[ATAB] ADD  CONSTRAINT [ATAB~0] PRIMARY KEY CLUSTERED

(

          [TABNAME] ASC,

          [VARKEY] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

Doug

[Former Member]

I found the duplicate entries in the T9DE1 pool table, below are the steps I followed.  I'm just checking with the business people before I remove the T9DE1 entries but I do not believe we are using it.

change entry in SAPPOOL.TSK file for row ATAB~0 from ok to ign, then restart sapinst

P ATAB~0 C ok

to

P ATAB~0 C ign

drop the ATAB primary index using SQL Server Studio:

USE [SBX]

GO

/****** Object:  Index [ATAB~0]    Script Date: 05/25/2012 10:45:35 ******/

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[sbx].[ATAB]') AND name = N'ATAB~0')

ALTER TABLE [sbx].[ATAB] DROP CONSTRAINT [ATAB~0]

GO

restart sapinst

runs until the following popup is displayed:

Not all objects are successfully processed. DIAGNOSIS: For details see output file with invalid objects invalid_objects.txt and log file object_checker.log. Normally it indicates the data load error but in some special cases (for example, if some objects were created or loaded externally) you can choose OK to continue.

this is due to skipping the ATAB primary index

verify that all records were loaded:

use SBX

select count(*) from sbx.ATAB

1,709,092 matches source database

when sapinst is done, find the duplicate ATAB record using SQL Server Studio:

use [SBX]

select TABNAME, VARKEY, count(*) from [sbx].[ATAB] group by TABNAME, VARKEY having count(*) > 1;

found 133 records in pool table T9DE1, Control table for deferred revenue, was only partly active

Remove the duplicate records.

then re-create the primary index:

USE [SBX]

GO

/****** Object:  Index [ATAB~0]    Script Date: 05/25/2012 10:45:36 ******/

ALTER TABLE [sbx].[ATAB] ADD  CONSTRAINT [ATAB~0] PRIMARY KEY CLUSTERED

(

          [TABNAME] ASC,

          [VARKEY] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

Doug