Sqlserver varbinary data want to select as varchar without truncation

Question

I'm running the following to retrieve my converted binary data, I then save the output to a file and it is truncated !

SELECT 
cast([BINARY_VALUE] as varchar(max)) BLOB_VALUE 
FROM [PROD3].[DOMAIN].[BLOBS]
WHERE OBJECT_ID = 881462

How can I save the whole of my converted binary data to a file using sqlserver?

how much big is the output – TheGameiswar Feb 17 '17 at 12:09 — TheGameiswar, Feb 17 '17 at 12:09

score 1 · Answer 1 · answered Feb 17 '17 at 12:18

1

did you try bcp command? It should work fine.

answered Feb 17 '17 at 12:18

Laeeq

Um ... no ... I don't know about BCP - is it built in already ?? – NottmTony Feb 17 '17 at 12:19
This is a case sensitive command which comes with SQL Server. If you are using windows authentication then you'll be fine with -T switch: Here is an example. c:\>bcp "" queryout c:\temp\data.txt -c -T -S SQLSERVERNAME – Laeeq Feb 17 '17 at 21:40

score 0 · Answer 2 · edited May 23 '17 at 12:00

0

Please check this question :

TL;DR: You should not use CAST, but CONVERT :

SELECT 
CONVERT(varchar(max), BINARY_VALUE,2) as BLOB_VALUE 
FROM [PROD3].[DOMAIN].[BLOBS]
WHERE OBJECT_ID = 881462

Note the use of the style parameter 2.

edited May 23 '17 at 12:00

Community

answered Feb 17 '17 at 11:20

Paul Daubian

SELECT CONVERT(VARCHAR(max), [BINARY_VALUE],2) as BLOB_VALUE FROM [PROD3].[DOMAIN].[BLOBS] WHERE OBJECT_ID = 881462 Above worked but DID NOT convert to readable string !!?? – NottmTony Feb 17 '17 at 11:51
1

Try converting to NVARCHAR instead of VARCHAR : `SELECT CONVERT(nvarchar(max), BINARY_VALUE,2) as BLOB_VALUE FROM [PROD3].[DOMAIN].[BLOBS] WHERE OBJECT_ID = 881462` – Paul Daubian Feb 17 '17 at 12:46
Nah... same result. CASTworks, CONVERT doesn't seem to, – NottmTony Feb 17 '17 at 13:26
Yes! Thanks! NVARCHAR did it! – Daniel May 02 '22 at 13:14

2 Answers2