Created
July 16, 2014 04:18
-
-
Save wislon/84149efe42e6dd540515 to your computer and use it in GitHub Desktop.
Export BLOB field from SQL Server to file on disk
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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!