如何使用 SQL 将图像从 SQL Server 保存到文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1673803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to save an image from SQL Server to a file using SQL
提问by Craig HB
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 作业不运行调度程序，该程序将使用 SQL Server 数据库邮件发送带有嵌入图像的电子邮件，如下所示：
exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = '[email protected]',
@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。
采纳答案by John Lemp
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 = '[email protected]',
@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 中引用它。您还需要为您的文件和清理生成一些唯一的名称，以便多个进程不会相互干扰。
回答by kristian
This articleexplains 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 的对象）来创建文件。
回答by David
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 字段保存到磁盘上的临时文件没有问题。
回答by Pavlo Svirin
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:
在此之后，插入的结果是：
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.
也许您将能够找到如何让它发挥作用的想法。
