How can I BCP queryout with filename from table?

Question

I'm extracting many BLOBs to files using BCP and wonder if there's a way to give each file a unique name using an ID field from the SQL table. This works:

DECLARE  @Command NVARCHAR(4000)  
SET @Command = 'bcp "SELECT FileData FROM MyDB.dbo.Attachments 
                WHERE Hist_ID = ''00004F13''" 
                queryout "C:\Wha.pdf" -T -c -C RAW -S SERVERNAME -U SA -P pa$$word'  
EXEC xp_cmdshell   @Command

But to get the filename, I've tried:

DECLARE @FileName varchar(50),  
@Command NVARCHAR(4000)  
SET @FileName = Hist_ID+'wha.PDF'  
SET @Command = 'bcp "SELECT FileData FROM MyDB.dbo.Attachments 
                WHERE Hist_ID = ''00004F13''"     queryout'  
SET @Command = @Command + 'C:\' + @FileName + '-T -c -C RAW 
                           -S SERVERNAME -U SA -P pa$$word'  
EXEC xp_cmdshell   @Command

I get an error: Invalid column name 'Hist_ID'. Is there some way to do this? Thanks for any help!

Answer 1

Try this. This is the only way I know have to execute one row at a time.

This code just prints the command. Uncomment the xp_cmdshell to make it actually run. You might want to start with a small sample first (by limiting the select)

DECLARE @Hist_ID VARCHAR(100)
DECLARE @Ext VARCHAR(10)
DECLARE @Command NVARCHAR(4000)  


DECLARE cAttachments CURSOR FOR
SELECT DISTINCT Hist_ID,Extension
FROM MyDB.dbo.Attachments 
ORDER BY Hist_ID

OPEN cAttachments

FETCH NEXT FROM cAttachments
INTO @Hist_ID, @Ext

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Command = 'bcp "SELECT FileData FROM MyDB.dbo.Attachments 
                WHERE Hist_ID = ''' + @Hist_ID + '''"
                queryout "C:\' + @Hist_ID + '.' + @Ext + '" -T -c -C RAW -S SERVERNAME -U SA -P pa$$word'  

    PRINT @Command

    -- EXEC xp_cmdshell   @Command


    FETCH NEXT FROM cAttachments
    INTO @Hist_ID, @Ext

END

CLOSE cAttachments
DEALLOCATE cAttachments