使用SQL Server存储过程将BLOB/Image等图片或二进制数据另存为磁盘文件

--要想下面的存储过程正常执行，必须先开启OLE对象的安全权限
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures',1;
GO
RECONFIGURE;
GO

--存储过程：将二进制数据保存到指定的目录
--需要先将@FilePathAndName所在目录的安全权限，添加Windows账户“Network Service”的写入权限，才会成功
CREATE PROCEDURE [dbo].[up_SaveBinaryToFile](@Binary VARBINARY(MAX), @FilePathAndName VARCHAR(MAX))
AS 
BEGIN
DECLARE @ObjectToken INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @ObjectReturn INT
	
	EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
	EXEC sp_OASetProperty @ObjectToken, 'Type', 1
	EXEC sp_OAMethod @ObjectToken, 'Open'
	EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'Write', NULL, @Binary
	IF (@ObjectReturn <> 0)
    BEGIN
        EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
        RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
    END
    
	EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FilePathAndName, 2
	EXEC sp_OAMethod @ObjectToken, 'Close'
	EXEC sp_OADestroy @ObjectToken
END
GO

--创建表[Temp_File]：如果在此表中插入新行时，isSave为1，则将文件保存到服务器指定的路径
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temp_File](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[uuid] [uniqueidentifier] NOT NULL,
	[title] [nvarchar](250) NULL,
	[name] [nvarchar](50) NULL,
	[ext] [nvarchar](50) NULL,
	[path] [nvarchar](max) NULL,
	[size] [int] NULL,
	[binData] [image] NULL,
	[comment] [nvarchar](max) NULL,
	[ownedBy] [nvarchar](50) NULL,
	[isSave] bit NULL	--如果为1，则将数据作为物理文件存储至[path]
 CONSTRAINT [PK_Temp_File] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Temp_File] ADD  CONSTRAINT [DF_Temp_File_uuid]  DEFAULT (newid()) FOR [uuid]
GO

--以下是通过触发器来调用：当在此表中新增一行时，则将文件保存到服务器指定的路径
CREATE TRIGGER tgr_exportTempFileTosvrDisk
ON Temp_File 
	INSTEAD OF INSERT
AS
	DECLARE @uuid uniqueidentifier
	DECLARE @title NVARCHAR(250)
	DECLARE @name NVARCHAR(50)
	DECLARE @ext NVARCHAR(50)
	DECLARE @isSave BIT	
	DECLARE @size INT
	DECLARE @fileData VARBINARY(MAX)
	DECLARE @comment NVARCHAR(MAX)
	DECLARE @ownedBy NVARCHAR(50)
		
	DECLARE @TempFolder NVARCHAR(256)
	DECLARE @SavedFile NVARCHAR(MAX)
	DECLARE @ObjectReturn INT
	DECLARE @Fso INT
	DECLARE @File INT	
	
	select @uuid = [uuid]
	  ,@title = [title]
      ,@name = [name]
      ,@ext = [ext]
      ,@isSave = [isSave]
      ,@size = [size]
      ,@fileData = [binData]
      ,@comment = [comment]
      ,@ownedBy = [ownedBy]
	  from inserted;	
	
	--文件写入 WINXP : C:\Documents and Settings\NetworkService\Local Settings\Temp
	--		   WIN7/8/10/2008/2012:   C:\Windows\ServiceProfile\NetworkService\AppData\Local\Temp\
	IF @isSave = 1
	BEGIN
		EXEC @ObjectReturn = sp_OACreate 'Scripting.FileSystemObject', @Fso OUTPUT;
		EXEC @ObjectReturn = sp_OAMethod @Fso, 'GetSpecialFolder', @File OUTPUT, 2; --系统临时文件夹
		EXEC @ObjectReturn = sp_OAGetProperty @File, 'Path', @TempFolder OUTPUT;
		SET @SavedFile = @TempFolder + N'\' + CONVERT(NVARCHAR(256), @uuid) + '.' + @ext;
		
		EXEC up_SaveBinaryToFile  @fileData , @SavedFile;
	END
	
	insert into Temp_File ([title],[name],[ext],[isSave],[size],[binData],[comment],[ownedBy]) values (@title,@name,@ext,@isSave,@size,@fileData,@comment,@ownedBy);
	update Temp_File set [path]=@SavedFile where [uuid]=@uuid
GO

------------------------------------------------------------
--以下是直接使用SQL语句，手动调用上述存储过程保存文件
DECLARE @fileData VARBINARY(MAX)
DECLARE @fileName NVARCHAR(255)
DECLARE @extName NVARCHAR(255)
DECLARE @filePath NVARCHAR(MAX)
select top 1 @fileData = binData, @fileName = [name], @extName= [type] from dbo.system_picsLib
SET @filePath = 'C:\temp\'+@fileName+'.'+@extName
exec up_SaveBinaryToFile  @fileData, @filePath