none
Error while replicating article from oracle to sql server

    Question

  • Hi All,

    I am facing an issue while replicating an article from oracle to SQL server as oracle is case sensitive while sql server is not.

    The Article has an primary key column which stores an value such as "TEST" and "test".but while replicating the article we are getting error stating duplicate value as sql server considers it as same.

    How to make Sql server case sensitive so that the record will be inserted properly without throwing any error.

    Thanks in advance.

    Wednesday, December 18, 2013 6:25 PM

All replies

  • Please verify that schema option 0x1000 is being used for the article in question.  This option replicates column-level collation and enables case-sensitive comparisons.

    Brandon Williams (blog | linkedin)

    Wednesday, December 18, 2013 6:44 PM
  • This is the detail error i am getting

    <dir>

    The process could not bulk copy into table '"dbo"."MPI_REASON_CODE"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
    Get help: http://help/MSSQL_REPL20037

    Batch send failed

    Violation of PRIMARY KEY constraint 'MSHREPL_31_PK'. Cannot insert duplicate key in object 'dbo.MPI_REASON_CODE'. The duplicate key value is (QACheckList). (Source: MSSQLServer, Error number: 2627)

    As my Source table has two records one with QAChecklist and QAchecklist which oracle considers as two rows but sql server considers it as one.
    </dir>
    Wednesday, December 18, 2013 7:33 PM
  • Please verify that schema option 0x1000 is being used for the article in question.  This option replicates column-level collation and enables case-sensitive comparisons.

    Brandon Williams (blog | linkedin)

    Wednesday, December 18, 2013 7:51 PM
  • Brandon i tried the thing provided by you and found this error

    Error messages:

    <dir></dir>
    <dir></dir><dir>

    The process could not bulk copy into table '"dbo"."MPI_REASON_CODE"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
    Get help: http://help/MSSQL_REPL20037

    Invalid object name 'dbo.MPI_REASON_CODE'. (Source: MSSQLServer, Error number: 208)
    Get help: http://help/208

    To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
    Get help: http://help/20253

    bcp "FlexNet"."dbo"."MPI_REASON_CODE" in "G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc\MESS01_DISTRIBUTION_MPI_SIM\20131218145213\REASON_CODE_17.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SCIDCSWDMESMPS01 -T -w (Source: MSSQLServer, Error number: 20253)
    Get help: http://help/20253

    </dir>
    Wednesday, December 18, 2013 8:56 PM
  • It could be permissions related or something else.

    You might want to enable verbose agent logging to get additional details.

    What happens when you execute the BCP command manually?  What is contained in the error file?


    Brandon Williams (blog | linkedin)

    Wednesday, December 18, 2013 9:14 PM
  • The BCP command also throws the same error.I check the folder and it doesnot contain any error file.

    I did an workaround of creating the table before replicating and it worked but i wanted to know why this error came as i have already set the option of "Drop existing object and create a new one" while replicating.

    Then i again replicated with the option 0x1000  but i found the same error as earlier "Violation of PRIMARY KEY constraint 'MSHREPL_31_PK'. Cannot insert duplicate key in object 'dbo.MPI_REASON_CODE'. The duplicate key value is (QACheckList)"

    the command i am using for adding article is

    use [distribution]
    exec sp_addarticle @publication = N'MPI_SIM', @article = N'REASON_CODE', @publisher = N'MESS01', @source_owner = N'FLXUSER', @source_object = N'REASON_CODE', @type = N'logbased', @description = N'', @creation_script = null, @pre_creation_cmd = N'drop', @schema_option =0x1000, @use_default_datatypes = 1, @destination_table = N'MPI_REASON_CODE', @destination_owner = N'dbo', @status = 0, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_FLXUSERREASON_CODE', @del_cmd = N'CALL sp_MSdel_FLXUSERREASON_CODE', @upd_cmd = N'SCALL sp_MSupd_FLXUSERREASON_CODE',@force_invalidate_snapshot = N'1'
    GO

    Is i am using the correct syntax for sp_article?..

    Wednesday, December 18, 2013 9:28 PM
  • remove the pk from the subscriber side table. Make sure you script it out before you do the drop. Then let the BCP operation complete and then try to re-apply the PK. You may need to remove the offending row.

    It appears the oracle snapshot agent is a little flakey.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, December 18, 2013 10:41 PM
  • Thanks Hilary,

    I follow the same process as an workaround for it..but i wanted to make the table case sensitive as it won't consider the data as duplicate.is there any way to make the table case sensitive.

    Thursday, December 19, 2013 3:12 PM