如何使用SQL将图像从SQL Server保存到文件 [英] How to save an image from SQL Server to a file using SQL
问题描述
在SQL Server 2005中，我有一个带有图像的表（数据类型：image）。仅使用SQL，如何将图像保存为文件（与SQL Server正在运行的同一服务器上）。如果我必须使用SQL CLR，但是如果可能，我想避免这种情况。
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作业运行没有调用一个proc将使用SQL Server数据库邮件发送带有嵌入图像的邮件的调度，如下所示：
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:
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';
SQL可以工作，但我需要先将图像保存为文件。如果我可以直接在电子邮件中获取图像，而不将其保存为文件，那没关系，但是它需要嵌入在电子邮件中，我只想使用SQL。
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.
推荐答案
我已经尝试使用@attach_query_result_as_file选项，但没有办法把这个查询写成二进制文件，我可以上班。所以下一个选项是将bcp用于临时文件，然后附加文件。
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.
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';
bcpFormat.fmt将如下所示：
The bcpFormat.fmt would look like this:
8.0
1
1 SQLIMAGE 0 0 "" 1 Image ""
将数据库中的图像作为文件附加到电子邮件中。它仍然不会显示内联，因为这将需要一些更多的工作，将图像编码到电子邮件的正文中，并从您的HTML引用它。您还需要为文件和清理生成一些唯一的名称，以便多个进程不会相互依赖。
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.
这篇关于如何使用SQL将图像从SQL Server保存到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
