Script to save varbinary data to disk
Solution 1
The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.
I found the following code here at the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
Solution 2
I am adding this to build on JohnOpincar's answer, so that others who want to use LinqPad can get a working solution faster.
/*
This LinqPad script saves data stored in a VARBINARY field to the specified folder.
1. Connect to SQL server and select the correct database in the connection dropdown (top right)
2. Change the Language to C# Program
3. Change "Attachments" to the name of your table that holds the VARBINARY data
4. Change "AttachmentBuffer" to the name of the field that holds the data
5. Change "Id" to the unique identifier field name
6. Change "1090" to the identity of the record you want to save
7. Change the path to where you want to save the file. Make sure you choose the right extension.
Notes: Windows 10 may give you "Access Denied" error when trying to save directly to C:\. Rather save to a subfolder.
*/
void Main()
{
var context = this;
var query =
from ci in context.Attachments
where ci.Id == 1090
select ci.AttachmentBuffer
;
byte[] result = query.Single().ToArray();
File.WriteAllBytes(@"c:\DEV\dumpfile.xlsx", result);
Console.WriteLine("Done");
}
Solution 3
You can use BCP, not T-SQL, but works well.
BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.txt" -T
Solution 4
If you have linqpad, this works:
void Main()
{
var context = this;
var query =
from ci in context.Images
where ci.ImageId == 10
select ci.Image
;
var result = query.Single ();
var bytes = Convert.FromBase64String(result);
File.WriteAllBytes(@"c:\image.bmp", bytes);
}
Solution 5
I know it's an old post, but I figured out why the following doesn't work and how to fix it:
BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.JPG" -T -N
The reason is bcp put Prefix Length at the very beginning of the file. It is either 4 bytes or 8 bytes, depends on data type of FileContent column (text, ntext, image: 4 varchar(max), varbinary(max) : 8 Refer to https://msdn.microsoft.com/en-us/library/ms190779.aspx)
Use a binary editor, like the one in Visual Studio, to remove the prefix bytes, and everything runs perfectly. :-)
Related videos on Youtube
Suraj
Hi! I'm Suraj and here is a little about me, on my blog That! But How?
Updated on November 12, 2020Comments
-
Suraj over 3 years
I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the bytes to disk (one file per row?) I'm sure this has been asked a thousand times before, but Googling comes up with mostly .net solutions. I want an SQL solution.
-
Suraj over 13 yearsHi Dustin - I was able to use command to output a file, but I don't think its working properly. The data is a serialized .net object. I know the data is stored properly because I have processes that operate on that data from .net. However, when I try to deserialize I get an error, which means that the bytes aren't written properly. Thoughts? If the command outputs a single varbinary(max) value, are the actual bytes written to disk or does the process include headers, etc.?
-
Thad over 10 yearsUsing the -N option seems to make this work properly
-
brz over 8 yearsUse the following options when prompted: - Enter the file storage type of field XXX [varbinary(max)]: <BLANK><ENTER> - Enter prefix-length of field XXX [8]: 0<ENTER> - Enter length of field XXX [0]: <BLANK><ENTER> - Enter field terminator [none]: <BLANK><ENTER> - Do you want to save this format information in a file? [Y/n] n<ENTER>
-
Ric .Net about 8 yearsthis works...? really, so
var context = this
will automatically search for your the instance of sql server, will login, will find the correct database, which has a scheme where the Images table exists... Never knew LinqPad was this smart!! -
JohnOpincar about 8 yearsHave you ever used it? It doesn't appear so. I guess irfandar and I are just imagining it.
-
Ric .Net about 8 yearsYes I use it, and yes your answer is correct, but only if you configure the connection correctly, but this information is missing from your answer and for new LinqPad users this will look like magic... That was the only point I was making
-
Gayan Dasanayake almost 8 yearsTo run this one might need to enable OLE Automation procedures msdn.microsoft.com/en-us/library/ms191188.aspx.
-
Dainius Kreivys over 6 yearsPrints out: SQLState = 37000, NativeError=2812 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Could not find stored procedure 'sp_descrive_first_result_set'.
-
Kiquenet about 6 yearsToad for Sql server free? link ?
-
David R Tribble almost 6 yearsYou need to specify a prefix-length of 0, instead of the default 8, so that the file length is not written as a prefix (header) before the file data.
-
David Walschots over 5 years@Vilhelm are you affiliated to Toad?
-
Vilhelm over 5 years@DavidWalschots No. I worked on Oracle databases with Toad and was frustraded that I could not do the same in MS Management Studio. Then I saw that Toad has an MSSQL version and the freeware had this feature so I use that now as my main editor for MSSQL databases.
-
CSharper over 4 yearsTo enable OLE Automation procedures use:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;