Binary constant doesn't download correctly at MSSQL

Question

I need to download some data to MSSQL table. So:

INSERT INTO [Project].[dbo].[table1] 
            ([version], 
             [date], 
             [xsd]) 
VALUES      ('3.3.3.3', 
             '20140520', 
             (SELECT * 
              FROM   OPENROWSET(BULK N'D:\File.XSD', single_clob) AS XSD)) 

go

As result at table inserts some file. it looks like: 0xDEFFFBF6C8F1...........46

So, now i copy this value to my script:

if exists (select Version from Table1 where Version ='3.3.3.3') update Table1 set    [Xsd]=0xDEFFFBF6C8F1...........  where Version ='3.3.3.3'
else insert into Table1 ([Version],[Xsd]) values ('3.3.3.3',0xDEFFFBF6C8F1...........

When it execute- i get value like that: 0x0DeFFF....4 .

I copy value to script correctly.. but cannot understand why last character disappears and i get zero at value.

Please, help me with that problem.

P.S. CREATE TABLE .... [Xsd] [image] NULL

P.P.S - so, i found some new things:

When i count lenght of downloaded string(from file)- it counts over 99k symbols. Then i copy and past it on script at Microsoft Management Studio - (if exists ...) - so, i get 32k symbols.

I dont know, that happens...

How the column [xsd] is defined? – Hamlet Hakobyan May 22 '14 at 12:09 — Hamlet Hakobyan, May 22 '14 at 12:09
CREATE TABLE .... [Xsd] [image] NULL – user2545071 May 23 '14 at 04:06 — user2545071, May 23 '14 at 04:06

score 1 · Accepted Answer · answered May 27 '14 at 12:24

from your PPS i understand you execute a SELECT query in Management Studio and copy the binary column from the result window. During this operation the string gets truncated to 32k Symbols.

Considering this, i think the problem is with Management Studio. Please check the preferences for the maximum characters retrieved:

Open from Menu Tools -> Options

Select from the tree on the left: Query Results -> SQL Server -> Results to Grid (Or Results to Text if you use that)

Both result options have restrictions on how many symbols they return.

Binary constant doesn't download correctly at MSSQL

1 Answers1