ayesamson

I won’t get into all of the “ins and outs” about BCP, but feel free to read up on the subject here: Import and Export Bulk Data by Using the bcp Utility (SQL Server)

Scenario

I have images stored in a SQL Server database and I need to access them without writing, purchasing or using a custom app. What can I do?

Solution

Step 1: Identify the table in question

In this case we are working with the Images table inside of the [devdb] database

Step 2: Generate a BCP format file

Create the format file by executing the following statement in a command prompt window.

bcp devdb.dbo.Images format nul -c -fimages.fmt -T -S (local)\DEV12 -v

This will produce a file named images.fmt in the C:\Temp\ directory

Step 3: Modify the format file

Open the newly pressed images.fmt with notepad. We will (very carefully) edit the contents. So here’s a look at the original file. Note: I modified the following image to fit the width of this layout. Meaning I cropped some white-space. Also note I have SQL 2005, 2008 R2 and SQL 2012 installed on my box. The 10.0 on the first line is present because the BCP version is from SQL 2008. It would show 11.0 if I were using BCP for SQL 2012.

Here is what it needs to look like after we make some adjustments. We modified the file to single out the image column. Be sure to SAVE your changes, especially changing SQLCHAR to SQLBINARY; otherwise, empty files will be created with a zero file size. Notice the white-space in the following image. I really don’t know if it makes a difference but the slight modifications I do make always work.

Step 4: Enable xp_cmdshell

If you don’t have xp_cmdshell enabled here’s the means to do so. Keep in mind if you don’t use xp_cmdshell often then be sure to disable it when you’re finished. You can search the subject in respects to security and xp_cmdshell.

EXEC sp_configure 'Show Advanced Options',1;
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE;
GO

Step 5: Write up query

In the event you don’t have images this is what I used to load up a few sample files. I literally used the sample pictures that came preloaded with windows 7. Which are located in the C:\Users\Public\Pictures\Sample Pictures directory.

/* LOAD TABLE */
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Chrysanthemum.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Chrysanthemum.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Desert.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Desert.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Hydrangeas.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Hydrangeas.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Jellyfish.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Jellyfish.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Koala.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Koala.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Lighthouse.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Lighthouse.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Penguins.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Penguins.jpg',SINGLE_BLOB) AS x;
INSERT INTO devdb.dbo.Images ([Image],[FileName]) SELECT BulkColumn, 'Tulips.jpg' FROM OPENROWSET(BULK 'C:\Users\Public\Pictures\Sample Pictures\Tulips.jpg',SINGLE_BLOB) AS x;

Let’s take a look at the contents of the table.

/* VERIFY TABLE CONTENTS */
SELECT id, Image, FileName FROM devdb.dbo.Images;

Step 6: Export images

Now that we know the table definition, adjusted the format file and enabled xp_cmdshell let’s extract some photos! We could run through this one-by-one but where’s the fun in that. I used a WHILE loop as opposed to a cursor to extract each photo by id.

/* EXPORT PHOTOS FROM DATABASE */
DECLARE @cnt INT, @rcnt INT, @cmd NVARCHAR(150), @fname NVARCHAR(50), @filepath NVARCHAR(50), @inst NVARCHAR(50)

/* SET VARIABLES */
SELECT @rcnt = COUNT (*) FROM [devdb].[dbo].[Images]
SET @cnt = 1
SET @filepath = 'c:\temp\'
SET @inst = '(local)\DEV12'

/* LOOP THROUGH RECORDS */
WHILE @cnt < @rcnt
	BEGIN
		SELECT @fname = FileName FROM devdb.dbo.Images WHERE (id = @cnt)
		SELECT @cmd = 'BCP "SELECT Image FROM [devdb].[dbo].[Images] WHERE (id = ' + CAST(@cnt AS VARCHAR) + ')" queryout "' + @filepath + @fname + '" -T -S ' + @inst + ' -f c:\temp\Images.fmt'
		--SELECT @cmd = 'BCP "SELECT Image FROM [devdb].[dbo].[Images] WHERE (id = ' + CAST(@cnt AS VARCHAR) + ')" queryout "c:\temp\' + @fname + '" -T -S (local)\DEV12 -f c:\temp\Images.fmt'
		PRINT @cmd /* PRINT STATEMENT TO SCREEN */
		EXEC xp_cmdshell @cmd
		SET @cnt = @cnt + 1
	END

Output

That’s pretty much it. Happy BCP’ing and exporting!