如何使用SQL将映像从SQL Server保存到文件

Question

In SQL Server 2005, I have a table with images (data type: image). Using only SQL, how do I save an image as a file (on the same server that SQL Server is running). If I have to, I'll use SQL CLR, but I want to avoid that if possible.

在SQL Server 2005中，我有一个包含图像的表（数据类型：图像）。仅使用SQL，如何将图像另存为文件（在运行SQL Server的同一服务器上）。如果必须，我将使用SQL CLR，但我想尽可能避免这种情况。

Background

I want a SQL Server job to run no a schedule that calls a proc that will send emails with embedded images using SQL Server Database Mail like this:

我希望SQL Server作业不运行调用proc的调度，该调度将使用SQL Server数据库邮件发送带有嵌入式图像的电子邮件，如下所示：

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'bob@hotmail.com',
@subject = 'hello',
@file_attachments = 'C:\MyLogo.gif',
@body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>', 
@body_format = 'HTML';

That SQL works, but I need to get the image saved as a file first. If I can get the image directly on the email without saving it as a file, that's fine, but it needs to be embedded on the email and I only want to use SQL.

该SQL有效，但我需要先将图像保存为文件。如果我可以直接在电子邮件中获取图像而不将其保存为文件，那很好，但它需要嵌入到电子邮件中，我只想使用SQL。

4 个解决方案

#1

I have tried using the @attach_query_result_as_file option but there is no way to have that query written as binary that I can get to work. So the next option would be to use bcp to a temp file and then attach the file.

我已经尝试使用@attach_query_result_as_file选项但是没有办法将该查询写成二进制文件，我可以开始工作。因此，下一个选项是将bcp用于临时文件，然后附加该文件。

DECLARE @sql VARCHAR(1000)

SET @sql = 'BCP "SELECT ImageColumn FROM YourTable where id = 1 " QUERYOUT C:\TEMP\MyLogo.gif -T -fC:\TEMP\bcpFormat.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'bob@hotmail.com',
@subject = 'test as image',
@body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>', 
@file_attachments = 'C:\TEMP\MyLogo.gif',
@body_format = 'HTML';

The bcpFormat.fmt would look like this:

bcpFormat.fmt看起来像这样：

8.0
1
1 SQLIMAGE 0 0 "" 1 Image ""

That will attach the image from your database to the email as a file. It still won't show it "inline" as that would take some more work to mime encode the image into the body of the email and reference it from your html. You would also need to generate some unique names for your files and cleanup so that multiple processes won't step on each other.

这会将数据库中的图像作为文件附加到电子邮件中。它仍然不会显示它“内联”，因为需要更多的工作来将图像编码到电子邮件的正文中并从您的html引用它。您还需要为文件生成一些唯一的名称并进行清理，以便多个进程不会相互衔接。

#2

This article explains how you can use SQL Server's OLE automation stored procs (which allow you to create and use COM-based objects through T-SQL) to create a file.

本文介绍如何使用SQL Server的OLE自动化存储过程（允许您通过T-SQL创建和使用基于COM的对象）来创建文件。

#3

Is an SQL CLR (.NET) stored procedure a possiblity? That would have no problem saving data from a blob field to a temporary file on disk.

SQL CLR（.NET）存储过程是否可能？将数据从blob字段保存到磁盘上的临时文件没有问题。

#4

-1

I think madC's comment is the best solution. You can also try using something like this :

我认为madC的评论是最好的解决方案。您也可以尝试使用以下内容：

INSERT INTO OPENROWSET ( 'BULK', '', BULK_BLOB ) ...

插入OPENROWSET（'BULK'，''，BULK_BLOB）......

but on my side it required the following additional steps:

但在我这边需要以下额外的步骤：

sp_configure 'show advanced options', 1
reconfigure with override
sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure with override

And after this the result of the INSERT was:

在此之后，INSERT的结果是：

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "BULK" for linked server "(null)".

Maybe you will be able to find the idea how to make it work.

也许你将能够找到如何让它发挥作用的想法。

#1

DECLARE @sql VARCHAR(1000)

SET @sql = 'BCP "SELECT ImageColumn FROM YourTable where id = 1 " QUERYOUT C:\TEMP\MyLogo.gif -T -fC:\TEMP\bcpFormat.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'bob@hotmail.com',
@subject = 'test as image',
@body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>', 
@file_attachments = 'C:\TEMP\MyLogo.gif',
@body_format = 'HTML';