Errrrrr this line is too long. You could've shown me at least the code
of the create table I would've believed you.
I've just tried even to dump the mp3 and pdf files with this code
I haven't noticed any corruption.
Show me please the output of your
OK - the Create table and insert is as follows:
CREATE TABLE TestFiles (
FileId bigint IDENTITY(1,1),
First of all I would strongly reccomend you to use
the varchar(max) type for the text files.
INSERT INTO TestFiles(FileDesc, FileData)
'This is my uploaded file',
(SELECT * FROM OPENROWSET(BULK '\\NTSERVER3\Users\Robert\Data\Dump1.txt', SINGLE_BLOB) as f);
Try to use (SELECT * FROM OPENROWSET(BULK '\\NTSERVER3\Users\Robert\Data\Dump1.txt', SINGLE_CLOB) in conjunction with the varchar(max)
When I bcp using your latest suggestion the output file contains ascii characters that are the hex representation of those ascii characters. So if the original file began with the characters 'pti', the output file begins '707469'. Hence the output file is exactly twice as long as the source file except that it has a carriage return line feed combination immediately after the strangely formatted data.
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.
Output from xp_msver is as follows:
Index Name Internal_Value Character_Value
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.1399.06
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.1399.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 91684864 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 15 0000000f
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 8191 8191 (8588959744)
20 Product ID NULL NULL
And yes I know I'm running this on a SQL installation that hasn't had the service packs installed yet.