Skip to content

Instantly share code, notes, and snippets.

@wislon
Created July 16, 2014 04:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save wislon/84149efe42e6dd540515 to your computer and use it in GitHub Desktop.
Save wislon/84149efe42e6dd540515 to your computer and use it in GitHub Desktop.
Export BLOB field from SQL Server to file on disk
-- because I keep having to do this at odd times and I keep having to look it up
use master
go
EXEC sp_configure 'show advanced options', 1
GO
Reconfigure
go
EXEC sp_configure 'xp_cmdshell', 1
GO
Reconfigure
go
/*********** REMEMBER TO TURN OFF THE show 'advanced options' and 'xp_cmdshell' afterwards! ***********/
use MyDatabaseName
-- SQL Server export image
DECLARE @Command NVARCHAR(4000)
DECLARE @DBServer NVARCHAR(100) = 'localhost\sqlexpress'
-- Keep the command on ONE SINGLE LINE
SET @Command = 'bcp "SELECT [file_field] FROM MyDatabaseName.dbo.attachments where attachment_id = 12345" queryout "C:\temp\test.pdf" -T -n -S'+@DBServer
-- This will export the file, but the file will appear corrupted if you try and open it.
-- This is because the first 16 bytes are used to indicate the length of the file (they're a
-- left-over from the SQL database binary stream), the bcp command doesn't strip them off.
-- Delete those first 16 bytes using a hex editor (or even Notepad++), save the file, and you
-- should then be able to open it.
PRINT @Command -- debugging
EXEC xp_cmdshell @Command
GO
/*********** TURN ON THE show advanced options and xp_cmdshell afterwards, but do it in reverse :) ***********/
use master
go
EXEC sp_configure 'xp_cmdshell', 0
GO
Reconfigure
go
EXEC sp_configure 'show advanced options', 0
GO
Reconfigure
go
@jfvoliveira
Copy link

Hi there. I've tried to do this on a pdf file but I can't get it to open. I've tried to remove those 16 bytes but the file is still corrupt.
Is there any way to prevent BCP to include those bytes? Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment