DECLARE @picture_id numeric(18,0), @organ_no varchar(81),
@picture_name nvarchar(255), @picture_size int
DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT picture_id, organ_no, picture_name, picture_size
FROM bi_t_outlet_pic
--where organ_no='200383'
OPEN my_cursor
FETCH FROM my_cursor INTO @picture_id, @organ_no, @picture_name, @picture_size
WHILE @@FETCH_STATUS = 0
BEGIN
/*{ ... Cursor logic here ... }*/
DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), picture, 1) FROM bi_t_outlet_pic WHERE picture_id = @picture_id AND organ_no=@organ_no);
DECLARE @Path nvarchar(1024);
SELECT @Path = 'd:\test';
DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @organ_no+'-'+@picture_name;
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;
FETCH FROM my_cursor INTO @picture_id, @organ_no, @picture_name,
@picture_size
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sql server 导出 image到文件
最新推荐文章于 2024-05-31 15:34:18 发布