How to retrieve varbinary(max) using sqlcmd.exe and T-SQL

Question

Post reply

How to retrieve varbinary(max) using sqlcmd.exe and T-SQL

shew

SSChampion

Points: 11389
More actions
October 13, 2011 at 6:26 pm

#244579

Is there a way to way to retrieve varbinary(max) using sqlcmd.exe and T-SQL?
Here is the test table definition that I am using:
CREATE TABLE Test_TextTable
(
FileName nvarchar(60),
FileType nvarchar(60),
Document varbinary(max)
);
Here are the contents of a test text file (which is easier to document in this forum than an image):
Line1
Line2
Line3
Line4
Line5
I can store the text file with the following:
sqlcmd.exe -S HRAC4\DSTANDALONE1 -E -w600 -b -l 32 -d dba -Q "INSERT INTO Test_TextTable(FileName, FileType, Document) SELECT 'temp.txt' AS FileName, '.ps1' AS FileType, * FROM OPENROWSET(BULK N'C:\cron\temp.txt', SINGLE_BLOB) AS Document;"
I can retrieve up to the first 4000 bytes with the following:
sqlcmd.exe -S HRAC4\DSTANDALONE1 -E -w600 -b -l 32 -d dba -Q "select convert(varchar(4000), document) from Test_TextTable;"
But I would like to have something that could retrieve a text file or an image larger than 4000 bytes. If the varbinary(max) column is a text file, I would like to display it with a select statement (as demonstrated above). If the varbinary(max) column is an image file, I would like to recreate the image so that I can display it with Internet Explorer (or anything else that displays images by default). I am trying to find a way to do this entirely from the command line and entirely with a T-SQL statement so I can add the code to a PowerShell script that I will be writing. One last thing, FILESTREAM is not an option for us.
Any ideas?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

shew SSChampion Points: 11389 More actions · Answer 1

This appears to come reasonably close to the output that I am looking for, but it uses bcp.exe instead of sqlcmd.exe:

bcp "select Document from dba.dbo.Test_TextTable" queryout "C:\cron\temp_out.txt" -S HRAC4\DSTANDALONE1 -T

Enter the file storage type of field Document [varbinary(max)]:

Enter prefix-length of field Document [8]:

Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] y

Host filename [bcp.fmt]:

Starting copy...

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 16 Average : (62.50 rows per sec.)

However, the output has stray characters in it. I haven't had a chance to test with an image yet.

PS C:\cron> cat temp_out.txt

# Line1

Line2

Line3

Line4

Line5

And the destination file size is different from the source file size:

PS C:\cron> dir

Directory: C:\cron

Mode LastWriteTime Length Name

---- ------------- ------ ----

d---- 5/25/2011 5:15 PM dba

-a--- 10/13/2011 9:16 PM 89 bcp.fmt

-a--- 10/13/2011 8:01 PM 35 temp.txt

-a--- 10/13/2011 9:16 PM 43 temp_out.txt

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 2

I believe you need to include the "RAW" option in order to keep from generating the "stray" charcters which are typically used to identify the file type internally to the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shew SSChampion Points: 11389 More actions · Answer 3

I tried "-C RAW" as follows, but I still got the "stray" characters.

bcp "select Document from dba.dbo.Test_TextTable" queryout "C:\cron\temp_out.txt" -S HRAC4\DSTANDALONE1 -T -C RAW

shew SSChampion Points: 11389 More actions · Answer 4

Jeff Moden (10/13/2011)
...in order to keep from generating the "stray" charcters which are typically used to identify the file type internally to the file.

Hmmm... I was taking all of the defaults earlier. Thanks for your input! If I enter "0" at the "Enter prefix-length of field Document [8]" prompt, instead of taking the default, I get better results.

PS C:\cron> bcp "select Document from dba.dbo.Test_TextTable" queryout "C:\cron\temp_out.txt" -S HRAC4\DSTANDALONE1 -T

Enter the file storage type of field Document [varbinary(max)]:

Enter prefix-length of field Document [8]: 0

Enter length of field Document [0]:

Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n]

Host filename [bcp.fmt]:

Starting copy...

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)

This generates an output file the same length as the input file:

PS C:\cron> dir

Directory: C:\cron

Mode LastWriteTime Length Name

---- ------------- ------ ----

d---- 5/25/2011 5:15 PM dba

-a--- 10/13/2011 9:36 PM 89 bcp.fmt

-a--- 10/13/2011 8:01 PM 35 temp.txt

-a--- 10/13/2011 9:36 PM 35 temp_out.txt

And no more stray text characters:

PS C:\cron> cat .\temp_out.txt

Line1

Line2

Line3

Line4

Line5

I'll try to test this out with an image file tomorrow.

shew SSChampion Points: 11389 More actions · Answer 5

Quick follow up...

I tested the code with an image, and it seemed to work the same as with a text file. Thanks again, Jeff.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Gents,

I would not use any of these methods, I would use a SQLCLR to pull the file in.. And lo and behold we have been talking about some SQLCLR objects in another thread today.. So I will post the link to mine on CodePlex..

http://nclsqlclrfile.codeplex.com/%5B/url%5D

I had used a command called textcopy.exe (I think that was its name) to get binary data into tables, HATED it but it worked. No I use SQLCLR..

CEWII

How to retrieve varbinary(max) using sqlcmd.exe and T-SQL

Cookies on SQLServerCentral