-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Declare @bcpCommand nvarchar(2000)

DECLARE @FileId int
DECLARE @Filename nvarchar(255)
Declare @FileType nvarchar(50)

Declare @currentFile nvarchar(255)
Declare @fixedFileName nvarchar(255)

DECLARE file_cursor CURSOR FOR
select
f.FileId, f.Filename, LOWER(SUBSTRING(f.filename, len(f.filename) - 2, 3)) as FileType
from dbo.Files f
ORDER BY f.FileId
OFFSET 0 ROWS
FETCH NEXT 500 ROWS ONLY;

OPEN file_cursor

FETCH NEXT FROM file_cursor
INTO @FileId, @Filename, @FileType

WHILE @@FETCH_STATUS = 0

BEGIN

set @fixedFileName = @Filename

set @fixedFileName = REPLACE(@fixedFileName, '/', '')
set @fixedFileName = REPLACE(@fixedFileName, '\\', '')
set @fixedFileName = REPLACE(@fixedFileName, ':', '')
set @fixedFileName = REPLACE(@fixedFileName, '%', '')

set @fixedFileName = RTRIM(LTRIM(@fixedFileName));

SET @currentFile = 'C:\temp\FileExport\' + convert(varchar(50), @FileId) + '-' + @fixedFileName

SET @bcpCommand = 'bcp "SELECT FileData From FileExport.dbo.Files Where FileId = ''' + convert(varchar(50), @FileId) + '''" queryout "'

SET @bcpCommand = @bcpCommand + @currentFile + '" -T '

IF (@FileType = 'pdf')
BEGIN
SET @bcpCommand = @bcpCommand + '-f C:\Temp\FileExport\Settings\PDFExportFormat.fmt'
END
ELSE IF (@FileType = 'png')
BEGIN
SET @bcpCommand = @bcpCommand + '-f C:\Temp\FileExport\Settings\PNGExportFormat.fmt'
END
ELSE IF (@FileType = 'jpg')
BEGIN
SET @bcpCommand = @bcpCommand + '-f C:\Temp\FileExport\Settings\JPGExportFormat.fmt'
END

print @bcpCommand

--EXEC xp_cmdshell @bcpCommand

FETCH NEXT FROM file_cursor
INTO @FileId, @Filename, @FileType

END

CLOSE file_cursor
DEALLOCATE file_cursor