通过存储过程从 SQL 导出二进制文件数据(图像) [英] Exporting binary file data (images) from SQL via a stored procedure

问题描述

我正在尝试导出大量图像文件，这些文件作为二进制数据在内部存储在 SQL 数据库中.

I am trying to export a fairly large number of image files, stored internally in an SQL database as binary data.

刚开始用 SQL 编写存储过程时，我遇到了一些关于如何存档的非常有用的指南，但我似乎遗漏了一些东西.

Being fairly new to writing stored procedures in SQL, I have come across a couple of very useful guides on how this can be archived, but I seem to be missing something.

我在本地运行 SQL Server 2008 R2，我正在尝试将文件写入 C:\ 驱动器上的文件夹.

I am running SQL Server 2008 R2 locally, and I am trying to write the files to a folder on my C:\ drive.

这是我目前所拥有的业务部分:

Here is the buisness part of what I have so far:

BEGIN
DECLARE @cmd VARCHAR(8000)
DECLARE @result int

DECLARE curExportBinaryDocs CURSOR FAST_FORWARD FOR
SELECT 'BCP "SELECT Photograph_Data FROM [ALBSCH Trial].[dbo].[Photograph] WHERE Photograph_ID = '
  + CAST(Photograph_ID AS VARCHAR(500)) + '" queryout "' + @OutputFilePath 
  + CAST(Photograph_ID AS VARCHAR(500)) + '.jpg"' + ' -n -T'
FROM dbo.Photograph

OPEN curExportBinaryDocs   
FETCH NEXT FROM curExportBinaryDocs INTO @cmd
WHILE @@FETCH_STATUS = 0
  BEGIN
     --PRINT @cmd
     EXEC @result = xp_cmdshell @cmd         
     FETCH NEXT FROM curExportBinaryDocs INTO @cmd
  END 
CLOSE curExportBinaryDocs
DEALLOCATE curExportBinaryDocs
END

'@result' 在 xp_cmdshell 调用之后总是被设置为 '1'(失败).所有表名/字段都是正确的，所以我怀疑我的 BCP 调用有问题，但我不确定接下来要尝试什么.

'@result' is always being set to '1' (failed) after the xp_cmdshell call. All the table names/fields are correct, so I suspect there is something wrong with my BCP call, but I am not sure what to try next.

非常欢迎任何帮助或建议.

Any help or advice would be very welcome.

推荐答案

嗯，首先..(对此很抱歉；))不要使用光标..抱歉我的帽子...

Well, first of all.. (and sorry about that ;) ) DON"T USE CURSORS.. and sorry for the caps...

关于游标的最糟糕的事情之一是它们可以锁定您的表.我总是为这些目的所做的(而且速度更快)，我使用 for 循环..像这样

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

对于下一部分，SQL Server 进程是否有足够的权限写入 c: 驱动器?另外，当您执行代码时查看您的消息窗格，也许您可​​以在那里找到一些东西?

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?

您还可以做什么，尝试手动执行.只需获取一个 BCP 语句并使用 xp_cmdshell 执行它.有没有报错?

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?

这篇关于通过存储过程从 SQL 导出二进制文件数据(图像)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！