问题描述
我正在尝试从 SQL Server 导出具有图像"数据类型的列.我使用了下面显示的代码.代码运行成功，但我没有看到输出文件.请让我知道这里可能存在什么问题.
I am trying to export a column with 'image' datatype from SQL server. I used the code shown below. The code runs successfully but i don't see the output file. Please let me know what the issue can be here.
DECLARE @x INT
declare @file varbinary(max)
set @file = (select convert(varbinary(max),fileContent) from table where submit_id = 123)
declare @filePath varchar(1024)
set @filePath = 'C:\Users\myusername\test.pdf'
EXEC sp_OACreate 'ADODB.Stream', @x OUTPUT
EXEC sp_OASetProperty @x, 'Type', 1
EXEC sp_OAMethod @x, 'Open'
EXEC sp_OAMethod @x, 'Write', NULL, @file
EXEC sp_OAMethod @x, 'SaveToFile', NULL, @filePath, 2
EXEC sp_OAMethod @x, 'Close'
EXEC sp_OADestroy @x
谢谢
推荐答案
我正在回答这个问题，因为我找到了一种更快的方法.
I am answering this question as i found out a way to do it faster.
从命令行使用 bcp(批量复制程序)实用程序可以保留本机文件格式并且速度非常快.输出文件也可以写入本地目录.此外，如果需要，可以自定义文件格式.
Using the bcp (bulk copy program) utility from the command line preserves the native file format and is very fast. The output files can be written to a local directory as well. Also, the file formats can be customized if needed.
使用我使用的代码添加更详细的答案版本.
Adding a more detailed version of the answer with the code i used.
1) 设置执行xp_cmdshell所需的权限.
1) Set the required permissions to execute xp_cmdshell.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE;
GO
2) 使用bcp
bcp schemaname.tablename format nul -T -n -f format_file_tablename.fmt
如果您不使用集成安全连接到数据库，请将 -T 替换为 -S servername -d databasename -U username -P password.
Replace -T with -S servername -d databasename -U username -P password if you don't connect to the database using integrated security.
3) 成功导出格式文件后，对其进行编辑以删除除image 或varbinary 列之外的所有其他列.
3) After a successful export of the format file, edit it to remove all the other columns except the image or varbinary column.
格式文件最初是这样的.
The format file looked like this initially.
11.0
17
1 SQLNCHAR 2 200 "" 1 Name SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 1000 "" 2 Description SQL_Latin1_General_CP1_CI_AS
3 SQLUNIQUEID 1 16 "" 3 GUID ""
4 SQLBIT 1 1 "" 4 Enabled ""
5 SQLNCHAR 2 600 "" 5 ClassType SQL_Latin1_General_CP1_CI_AS
6 SQLINT 0 4 "" 6 PartitionID ""
7 SQLBIT 1 1 "" 7 Protected ""
8 SQLDATETIME 1 8 "" 8 LastModifiedTime ""
9 SQLINT 0 4 "" 9 LastModifiedByID ""
10 SQLINT 0 4 "" 10 ImageType ""
11 SQLBIT 1 1 "" 11 Template ""
12 SQLINT 0 4 "" 12 ObjectID ""
13 SQLBINARY 8 0 "" 13 Image --column of interest ""
14 SQLINT 0 4 "" 14 ParentId ""
15 SQLNCHAR 2 600 "" 15 ParentClassType SQL_Latin1_General_CP1_CI_AS
16 SQLBIT 1 1 "" 16 IsPrimary ""
17 SQLDATETIME 1 8 "" 17 ImageCaptureDate ""
我编辑了如下文件.
11.0
1
1 SQLBINARY 0 0 "" 1 Image ""
4) 然后我不得不遍历表中的行以将每行中的图像列提取为一个文件.为此，我使用了 临时表.
4) Then i had to loop through the rows in the table to extract image column in each row as a file. I used a temp table for this purpose.
IF OBJECT_ID('dbo.tmp_for_picture', 'U') IS NOT NULL
DROP TABLE tmp_for_picture
GO
select
row_number() over(order by parentid) as rownum
,i.image as image_column
,i.parentid
,replace(p.name,',','') as picture_file_name
,i.name
into tmp_for_picture
from Images i
join personnel p on p.ObjectID = i.ParentId
GO
declare @cnt int
declare @i int
declare @filename varchar(512)
declare @extension varchar(20)
declare @sql varchar(4000)
set @cnt = (select count(*) from Images i join personnel p on p.ObjectID = i.ParentId)
set @i = 1
set @extension = '.jpeg' --or extract the extension from a column in the table if available
while @i <= @cnt
begin
--print @i
set @filename = (select picture_file_name from tmp_for_picture where rownum = @i)
set @sql = 'bcp "select image_column from tmp_for_picture where rownum = '+str(@i)+'" queryout "F:\pictures\'+@filename+@extension+'" -f formatfile.fmt -S servername -d databasename -T'
--print @sql
exec xp_cmdshell @sql
set @i = @i+1
end
GO
上述步骤可用于从数据库中提取任何类型的图像/varbinary 文件(存储为 pdf、docx 等).
The steps described above, can be used to extract any type of image/varbinary files (stored as pdf,docx etc.) from the database.
这篇关于将图像列导出到 sql server 中的 pdf 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！