Exporting binary file data (images) from SQL via a stored procedure
Well, first of all.. (and sorry about that ;) ) DON"T USE CURSORS..
and sorry for the caps...
One of the most baddest things about cursors are that they can lock your table. What i always do for these purposes (and which is quite faster), i use a for loop.. like this
declare @totrow int
, @currow int
, @result int
, @nsql nvarchar(max)
declare @sqlStatements table (
Id int identity(1, 1)
, SqlStatement varchar(max)
)
insert
into @sqlStatements
select 'QUERY PART'
from table
set @totrow = @@rowcount
set @currow = 1
while @totrow > 0 and @currow <= @totrow
begin
select @nsql = SqlStatement
from @SqlStatements
where Id = @currow
exec @result = xp_cmdshell @nsql
set @currow = @currow + 1
end
For the next part, does the SQL Server process has enough permission to write to the c: drive? Also, look into your message pane when you execute your code, maybe you can find something there?
What you also can do, try to execute it manually. Just get one BCP statement and execute it with the xp_cmdshell. Does it gives any errors?
Export images from a SQL Server using Stored procedures
try the following query:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
EXEC master..xp_cmdshell 'mkdir C:\ImagesFromSql'
DECLARE @ID as int
DECLARE @SQL as varchar(4000)
DECLARE csr CURSOR FOR SELECT ID FROM [FootballTeam].[dbo].[Players]
OPEN csr
FETCH NEXT FROM csr INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'BCP "SELECT Photo FROM [FootballTeam].[dbo].[Players] WHERE ID = "' + CAST(@ID as varchar(10)) + ' queryout "C:\ImagesFromSql\' + CAST(@ID as varchar(10)) + '.jpg" -T -N'
EXEC master..xp_cmdshell @SQL
FETCH NEXT FROM csr INTO @ID
END
CLOSE csr
DEALLOCATE csr
How to export image field to file?
-- Write all database images (jpg) to file.
--------- --------- --------- --------- --------- --------- ---------
DECLARE CURSOR_ProductIds CURSOR FOR (SELECT ImgImagesId FROM ImgProductSample)
DECLARE @ProductId INT;
OPEN CURSOR_ProductIds
FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = @ProductId);
DECLARE @Path nvarchar(1024);
SELECT @Path = 'C:\MyImages\Output';
DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = @ProductId);
DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Filename;
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;
FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId
END
CLOSE CURSOR_ProductIds
DEALLOCATE CURSOR_ProductIds
-- Make sure the following statement is executed to enable file IO
-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx
--------- --------- --------- --------- --------- --------- ---------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Send image stored in image field in body of Email sp_dbsend_mail
So I finally got this sorted and working in with my other code - ended up being able to export via cursor and when finished delete the folder contents - whilst dbsendmail added the attachment I was also able to embed into body of the email - code as follows for anyone interested! - I know its far from perfect so any assistance on cleaning up is appreciated - true novice alert!!! Hope it makes sense what I was trying to do - @TT thanks for your efforts and sorry for a - not being clear - b - not being clear - c - blah blah!!! - BR Paul
DECLARE @IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT,
@MYdtDate NVARCHAR(50),
@MYemail NVARCHAR(50),
@MYszBlockCapitols NVARCHAR(50),
@MYdwJobnumber NVARCHAR(50),
@MYszDelpostCode NVARCHAR(50),
@MYszDelAddrName NVARCHAR(50),
@MYszJobOrderNum NVARCHAR(50),
@MYszDelAddr4 NVARCHAR(50),
@MYszAccCode NVARCHAR(20),
@MYdtTime NVARCHAR(10),
@body NVARCHAR(4000)
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT ISNULL(JobItem.dwJobNumber,' '), ISNULL(JobItem.szDelAddrName,' '), ISNULL(JobItem.szDelPostCode,' '), ISNULL(Signature.szBlockCapitols,' '), ISNULL(StirlingManager_Comp1.dbo.SalesAccounts.szReportText8,' '), Convert(varchar,Signature.dtDate,103) AS Date, Convert(varchar,Signature.dtDate,108)as Time, ISNULL(JobItem.szJobOrderNum,' '), ISNULL(JobItem.szDelAddr4,' '), ISNULL(JobItem.szAccCode,' '), ISNULL(dms_document.data,' ')
FROM Transport_Comp1.dbo.JobItem INNER JOIN
Transport_Comp1.dbo.dms_document ON JobItem.dwJobItemId = dms_document.dwJobItemId LEFT OUTER JOIN
Transport_Comp1.dbo.Signature ON dms_document.id = Signature.dwDocumentIdFK LEFT OUTER JOIN
DocArchive_Comp1.dbo.dms_document AS dms_document_1 ON dms_document.id = dms_document_1.id LEFT OUTER JOIN
StirlingManager_Comp1.dbo.SalesAccounts ON JobItem.szAccCode = StirlingManager_Comp1.dbo.SalesAccounts.szAccountCode
WHERE (Signature.dtDate > DATEADD(MINUTE, -800, GETUTCDATE())) AND (Signature.dtDate < DATEADD(SECOND, -0.001, GETUTCDATE())) AND (Signature.bArrived = 0) AND (StirlingManager_Comp1.dbo.SalesAccounts.szReportText8 > '')
OPEN C1
FETCH NEXT FROM C1 INTO
@MYdwJobnumber, @MYszDelAddrName, @MYszDelPostCode, @MYszBlockCapitols, @MYemail, @MYdtDate, @MYdtTime,@MYszJobOrderNum, @MYszDelAddr4, @MYszAccCode, @IMG_PATH
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @TIMESTAMP = 'd:\Signatures-TMP\' + @MYdwJobnumber + '.bmp'
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
SET @body='<html><body><p><img src="http://www.mycompany.co.uk/wp-content/uploads/2013/12/mycompany.png" alt="Sample Image" align="left"></p>
<br><br><br>
<p><span style="font-size: 9px; font-family: Verdana, Geneva, sans-serif; color: #0d3172;"><strong>My Company<br><span style="Century Gothic;">My Company address</span></span></p>
<br><br></strong>
<h1><span style="font-size: x-large; font-family: arial, helvetica, sans-serif; color: #0d3172;">DELIVERY CONFIRMATION</span></h1>
<br>
<table style="border: 2px double #0d3172; color: #0d3172; font-size: 12pt; font-family: arial, helvetica, sans-serif; width: 800px;" border="1" cellspacing="0" cellpadding="4">
<tbody>
<tr>
<td><span style="font-size: small;">YOUR REFERENCE</span></td>
<td width="590"><span style="font-size: small;"><strong>' + @MYszJobOrderNum + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">DELIVERY TO</span></td>
<td><span style="font-size: small;"><strong>' + @MYszDelAddrName + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">TOWN</span></td>
<td><span style="font-size: small;"><strong>' + @MYszDelAddr4 + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">POSTCODE</span></td>
<td><span style="font-size: small;"><strong>' + @MYszDelPostCode + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">DELIVERY DATE</span></td>
<td><span style="font-size: small;"><strong>' + @MYdtDate + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">DELIVERY TIME</span></td>
<td><span style="font-size: small;"><strong>' + @MYdtTime + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">SIGNED FOR BY</span></td>
<td><span style="font-size: small;"><strong>' + @MYszBlockCapitols + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">YOUR ACCOUNT CODE</span></td>
<td><span style="font-size: small;"><strong>' + @MYszAccCode + '</strong></span></td>
</tr>
<tr>
<td><span style="font-size: small;">OUR JOB NUMBER</span></td>
<td><span style="font-size: small;"><strong>' + @MYdwJobnumber + '</strong></span></td>
</tr>
</tbody>
</table>
<br><br>
<p><span style="font-size: 12px; font-family: Verdana, Geneva, sans-serif; color: #0d3172;"><strong>DIGITAL SIGNATURE</p></strong></span>
<img src="cid:'+ @MydwJobNumber + '.bmp" /><p></p>
<p><span style="font-size: 12px; font-family: Verdana, Geneva, sans-serif; color: #0d3172;"><strong>Please contact our Customer Services Team if you have any questions or queries.</span></strong></span></p></body>
</html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL_Email',
@recipients = @MYemail,
@subject = 'EMAIL POD',
@file_attachments = @TIMESTAMP,
@body=@body,
@body_format = 'HTML';
print @body;
FETCH NEXT FROM C1 INTO
@MYdwJobnumber, @MYszDelAddrName, @MYszDelPostCode, @MYszBlockCapitols, @MYemail, @MYdtDate, @MYdtTime,@MYszJobOrderNum, @MYszDelAddr4, @MYszAccCode, @IMG_PATH
END
EXEC xp_cmdshell 'del D:\Signatures-TMP\*.bmp'
CLOSE C1
DEALLOCATE C1
What’s the easiest way to preview data from an image column?
I would write a proc (or query; see below) to export the binary out to the file system and then use any old off the shelf photo management utility (i.e. Windows Photo Viewer) to take a look at what's inside.
If your clever in your file naming you could give yourself enough information about each image in the name to quickly find it in the database again once you've visually located what your looking for.
Here is a proc that will export binary to the file system. I modified from this sample code. It's untested but should be extremely close for concept. It's using BCP to export your binary. Check here for the full docs on the BCP utility.
The proc also gives you the ability to export everything in the table, or only a single row based on a the passed primarykey. It uses a cursor (yuck), as well as some dynamic sql (yuck, yuck) but sometimes you gotta do what you gotta do.
CREATE PROCEDURE ExportMyImageFiles
(
@PriKey INT,
@OutputFilePath VARCHAR(500)
)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
IF @PriKey IS NULL /* export all images */
BEGIN
DECLARE curExportBinaryImgs CURSOR FAST_FORWARD FOR
SELECT 'BCP "SELECT MyImage FROM [dbo].[MyTable]
WHERE PrimaryKey =' + CAST(PrimaryKey AS VARCHAR(25)) +
'" queryout ' + @OutputFilePath + MyImageName + '.' +
MyImageType + ' -S MyServer\MyInstance -T -fC:\Documents.fmt'
FROM [dbo].[MyTable]
OPEN curExportBinaryImgs
FETCH NEXT FROM curExportBinaryImgs INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC xp_cmdshell @sql, NO_OUTPUT
FETCH NEXT FROM curExportBinaryImgs INTO @sql
END
CLOSE curExportBinaryImgs
DEALLOCATE curExportBinaryImgs
END
ELSE /* Export only the primary key provided */
BEGIN
SELECT @sql = 'BCP "SELECT MyImage FROM [dbo].[MyTable]
WHERE PrimaryKey =' + CAST(PrimaryKey AS VARCHAR(25)) +
'" queryout ' + @OutputFilePath
+ MyImageName + '.' + MyImageType +
' -S MyServer\MyInstance -T -fC:\Documents.fmt'
FROM [dbo].[MyTable]
WHERE PrimaryKey = @PriKey
EXEC xp_cmdshell @sql,NO_OUTPUT
END
END
This is all assuming of course that what is stored in your Image column is actually an image and not some other file type. Hopefully if it is an image you also know the type, bmp, jpg, png, gif, etc.
If you don't want the hassle or reusability of a full blown proc try single query like this:
DECLARE @OutputFilePath VarChar(500) = /* put output dir here */
DECLARE @sql VARCHAR(8000)
DECLARE curExportBinaryImgs CURSOR FAST_FORWARD FOR
SELECT 'BCP "SELECT MyImage FROM [dbo].[MyTable]
WHERE PrimaryKey =' + CAST(PrimaryKey AS VARCHAR(25)) +
'" queryout ' + @OutputFilePath + MyImageName + '.' +
MyImageType + ' -S MyServer\MyInstance -T -fC:\Documents.fmt'
FROM [dbo].[MyTable]
OPEN curExportBinaryImgs
FETCH NEXT FROM curExportBinaryImgs INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC xp_cmdshell @sql, NO_OUTPUT
FETCH NEXT FROM curExportBinaryImgs INTO @sql
END
CLOSE curExportBinaryImgs
DEALLOCATE curExportBinaryImgs
Related Topics
How to Find Row Number of a Record
Generate Series of Week Intervals for Given Month
Rails Brakeman Warning of SQL Injection
How to Use Left & Right Functions in SQL to Get Last 3 Characters
How to Increase Dbms_Output Buffer
Sqlite Unique Key with a Combination of Two Columns
Grant Permission to Create Tables - SQL Server
Sql Server 2005:Charindex Starting from The End
Sql: How to Select a Max Value for Each Group Per Day
Varchar(255) V Tinyblob V Tinytext
Pass Parameter in Table Valued Function Using Select Statement
How to Find Out If an Oracle Database Is Set to Autocommit
How This SQL Injection Works? Explanation Needed
Ant SQL Task: How to Run SQL and Pl/Sql and Notice Execution Failure
What to Replace Left Join in a View So I Can Have an Indexed View