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??Did you try the DTS approach?|||...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 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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)|||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|||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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)

Thanks. Ill go through it