Step 1: Enable xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.
Step 2: Create the bcpFormat.fmt file
Step 3: Query the BLOB table
select 'EXEC master.dbo.xp_CmdShell ''BCP "select blobField FROM blobTable where idField = ' + CAST(idField as varchar(10)) + '" QUERYOUT C:\TEMP\' + filenameField + ' -T -fC:\TEMP\bcpFormat.fmt''' from blobTable
- Make sure to use the proper field (blobField, idField, filenameField) and table names (blobTable)
- You could add a WHERE clause to filter the rows that you export
- Make sure that the output path and *.fmt file exist and have necessary permissions
Step 4: Execute the BCP statements
- Select all of the rows in the result grid
- Open a new (empty) query window