BCP.exe vs Textcopy.exe for Blobs data transfer | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

BCP.exe vs Textcopy.exe for Blobs data transfer

Hi, I am performing a database migration from SQL6.5 to SQL2000. Database objects are created in SQL2000 using EM generated script. Data is transfer using BCP utility. To my understanding, for normal data type I need to use BCP.exe to make the data transfer. For Blobs (text, ntext, image) I have to use TextCopy.exe to transfer only the blobs data separately. After I finished the data transfer using BCP.exe, I do a select and found that text cols are also migrated from SQL6.5 to SQL2000. I thought by default, text col stored a 16-bit pointer to separate data pages for blobs. Logically, text pointer should be BCP OUT and BCP IN. Thus, that#%92s why TextCopy.exe is needed to transfer blobs data only to blobs cols separately. Now I am confused! Can BCP.exe also transfer Blobs data? If yes, when to use Textcopy.exe?
Can someone enlighten me, please… Thank you

BCP will export all data. Textcopy.exe can be used to import/export actual files like mypic.jpg or mydoc.doc.
See if this provides additional informations:http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx
Frank Kalis
SQL Server MVP

I forgot to mention that all tables with Blob data type are In-Row Text disabled (meaning is using Out-Row Text). Argyle, Can I conclude that BCP.exe will not exportimport blobs cols that stored pic (jpg, gif) or document (.doc, pdf) files? However, BCP.exe will still exportimport purely ‘text#%92 data on text, ntext columns
Is that what you mean?

No what I’m saying is that bcp.exe will export/import all data including text,image etc. textcopy.exe is just a tool to handle export/imports of "blob" files a bit easier. You are importing a file directly into an image field. You are not working with comma seperated files with format files etc. Just simple files. With textcopy.exe you can say "import mypic.gif into my image field on row with id X". That is not as easy to do with bcp.