Heterogenous Database in SQL Server Replication

  • Hello,

    I am using SQL Server 2008 on XP machine and want to Replicate a database to an Oracle Database on a Unix machine. The two machines are connected to each other on network. I am successfully able to perform snapshot replication for small tables. But there is one table with a 2 columns of datatype VARCHAR(5000) and few other columns of datatype of VARCHAR(smaller than 4000). This table has over 1 million recrods. The distribution agent fails to apply the snapshot on the Oracle database for this table.

    It says:

    " Error 20203: There is not Enough Storage to Complete this Operation". I tried to repair my MDAC as well and update it, that too didn't worked.

    Can some please help me.

    Thanks and Regards

    Sameer Kumar

    Associate DBA

  • you need to speak to an oracle dba on this

    i'm not 100% sure on this but i beleive you need to change the target column in the oracle database to VARCHAR2

    MVDBA

  • actually - i will just correct myself

    you need to store the target column as text or LOB

    varchar2 has a limit of 4000 byes

    varchar has a length of 2000 bytes in oracle

    MVDBA

  • For SQL Server varchar() not larger than 4000 bytes Oracle's varchar2() would do the trick.

    For SQL Server varchar() larger than 4000 bytes receiving Oracle column should be a CLOB.

    Since CLOB - LOBs in general - large than 4000 have to be manipulated via dbms_lob system package I'm not sure how SQL Server would manage it.

    Would you consider telling us the outcome?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thanks to all for a prompt reply.

    For SQL Server varchar() larger than 4000 bytes receiving Oracle column should be a CLOB.

    SQL Server Replication to Oracle Subscriber converts VARCHAR2(greater than 4000) to CLOB. I need not do it explicitly (in fact there is no choice for VHARCHAR datatype, only timestamp type can be mapped to binary).

    Would you consider telling us the outcome?

    Still, I tried to create the table (with LOB) in Oracle and set the if EXISTS to "truncate the tables" (That is if the tables already exists in target Oracle DB they will be truncated). This too did not worked.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply