First of all I would strongly reccomend you to use the varchar(max) type for the text files.
For the purposes of experimentation I did so but really I'm looking for a generic solution to store and retrieve any kind of file to and from a database.
Try to use (SELECT * FROM OPENROWSET(BULK '\\NTSERVER3\Users\Robert\Data\Dump1.txt', SINGLE_CLOB) in conjunction with the varchar(max)
I did this with the amended table structure.
Ok looks like by default the bcp dumps the file using the UTF8 that what makes your
file twice as big - for each character it takes two bytes although you have to use -w
option in order to dump the text file using Unicode characters instead of -N for binaries.
So try to use e.g.
bcp "select field from yourdb.table" -T -c -C 1250.
The output file was 2 bytes larger than the input file and those two bytes were an extra carriage return line feed combination on the end of the output file.
I'm beginning to wish I'd never started this!