Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: Migrating CLOBS and BLOBS to SQL SERVER from ORACLE 9i

    I am taking over a Database set up where we have 2 databases. One is a SQL SERVER 2000 db and the other is an Oracle 9i db.

    THe previous DBA had/has a problem with transferring CLOB and Blob datatypes From the ORACLE db to SQL SERVER. He says that the OLEDB driver SQL SERVER provides has limitations particularly on the CLOB data because it truncates data over 4000 characters in length.


    For the Blob issue:
    We basically want to store/move attachments (word documents etc)
    into image datatypes (SS) from the blob datatype (9i).

    I figured I could do this using DTS or even using the Linked server method. I figured worst case scenario I would just store the documents on the server and then use SQL SERVER's textcopy utility to upload the documents into the image datatype column of the SQL SERVER table.

    For the Clob Issue I have no idea what to do, because I cant find anything online that mentions SQL SERVER having issues with importing CLOB data over 4000 charcters in length, afterall the text datatype accomdates more than 4000 characters.

    Does anyone have any suggestions for moving the Blob and most importantly the Clob datatypes from 9i to SQL SERVER without any truncation in the data????

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Did you try the DTS approach?
    -bpd

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    ...and you realize this will retart the firestorm argument about whether it is better to store BLOBs in the database, or in the File System with a simple link in the database...
    -bpd

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think there is a limitation. I usually do a BCP to load them in. Check out this site for some good stuff:

    TEXT, NTEXT, and IMAGE datatypes - Tutorial

  5. #5
    Join Date
    Mar 2002
    Posts
    162
    Originally posted by bpdWork
    ...and you realize this will retart the firestorm argument about whether it is better to store BLOBs in the database, or in the File System with a simple link in the database...
    I dont think DTS has an issue with migrating Blobs. It does have issues with migrating Clobs with data over 4000 characters in Length


    p.s. LOL about starting a firestorm

  6. #6
    Join Date
    Mar 2002
    Posts
    162
    Originally posted by rdjabarov
    I don't think there is a limitation. I usually do a BCP to load them in. Check out this site for some good stuff:

    TEXT, NTEXT, and IMAGE datatypes - Tutorial
    Thanks. Ill go through it

Posting Permissions

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