Data Replication 2
Results 1 to 3 of 3

Thread: How to handle calculated column and BLOB, CLOB field data

  1. #1
    pawan.mantha@gmail.com is offline Junior Member
    Join Date
    Aug 2019
    Posts
    2
    Rep Power
    0

    How to handle calculated column and BLOB, CLOB field data

    We trying to Move Data from Oracle to SQL server 2017



    • Destination has got a calculated column but not source. This is failing the load for the table. Except exclusion of such tables do we have any other option?



    • BLOB, CLOB field data is getting truncated to 8192 and 8190 size respectively in SQL Varbinary (max)/Varchar(max). How do we handle this?

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    161
    Rep Power
    12
    * Calculated Columns
    They should be no problem. What is the failure/error-message?
    Please check the '<replicate>\data\logs\reptask_xxx.log' file for message and resolve, or provide relevant details here.
    Please note that the default for Replicate is to Drop-and-Create target tables, just like they are on source.
    For target tables with computed-by columns you probably want to change that to 'Truncate' to allow you to pre-create the target table just the way you like it and keep it that way.

    * LOBs
    Please check DESIGNER - Task Settings
    Metadata --> Target Metadata -->Replicate LOB columns --> Limit LOB size to (KB)

    hth,
    Hein

  3. #3
    pawan.mantha@gmail.com is offline Junior Member
    Join Date
    Aug 2019
    Posts
    2
    Rep Power
    0
    Hi Hein,

    Please see responses inline below and help me how to move further.

    Quote Originally Posted by Hein View Post
    * Calculated Columns
    They should be no problem. What is the failure/error-message?
    Please check the '<replicate>\data\logs\reptask_xxx.log' file for message and resolve, or provide relevant details here.
    Please note that the default for Replicate is to Drop-and-Create target tables, just like they are on source.
    For target tables with computed-by columns you probably want to change that to 'Truncate' to allow you to pre-create the target table just the way you like it and keep it that way.

    Pavan:

    • RetCode: SQL_ERROR SqlState: 42000 NativeError: 271 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The column " CAL " cannot be modified because it is either a computed column or is the result of a UNION operator. Line: 1 Column: -1
    • Failed to prepare statement 'INSERT INTO [Demo].[table1]([aa],[bb],[cc],[STARTDATE],[abc],[CAL]) values (?,?,?,?,?,?)'



    • For an example we are creating table in SQL and ‘CAL’ was the computed column




    CREATE TABLE [Demo].[table1](
    [aa] [nvarchar](50) NOT NULL,
    [bb] [nvarchar](50) NOT NULL,
    [cc] [nvarchar](50) NOT NULL,
    [STARTDATE] [datetime2](0) NOT NULL,
    [abc] [float] NOT NULL,
    [CAL] AS (dateadd(day,[abc]/(1440),[STARTDATE])) PERSISTED
    ) ON [PRIMARY]
    GO


    * LOBs
    Please check DESIGNER - Task Settings
    Metadata --> Target Metadata -->Replicate LOB columns --> Limit LOB size to (KB)

    Pavan:
    I tried (*) Allow unlimited LOB size then got an Error:” The source endpoint does not support replication of full LOB columns. To replicate LOB columns, enable "Limited-size LOB mode" in the Task Settings > Target Metadata tab”



    hth,
    Hein

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •