SQL Server 2000：如何将Image变量从存储过程保存到文件系统中的文件

问题描述

我有一个存储过程，它接收 @Data image 参数。

I have a stored procedure that receives a @Data image parameter.

我想从存储过程将其保存到文件系统。

And I want to save it to the file system from a stored procedure.

知道怎么做吗？

推荐答案

您可以使用OLE自动化（ADODB.Stream）从SQL Server 2000存储过程写入二进制数据，如和。下面的示例使用varbinary变量，但它应该与您的图像参数类似。

You can use OLE automation (ADODB.Stream) to write binary data from a SQL Server 2000 stored procedure, as described here and here. The sample below uses a varbinary variable, but it should work similarly with your image parameter.

DECLARE @Path VarChar(255)
DECLARE @Data VarBinary(1000)
SET @Data = 0x12345678
SET @Path = 'c:\test.dat'

DECLARE @object Integer
DECLARE @hr Integer
-- create the ADO stream object
EXEC @hr = sp_oacreate 'ADODB.Stream', @object OUTPUT, 1
IF (@hr <> 0)
   RAISERROR('ADO stream creation failed', 11, 0)
-- configure it for binary access
EXEC @hr = sp_oasetproperty @object, 'Type', 1
IF (@hr <> 0)
   RAISERROR('Failed to set stream type', 11, 0)
-- open the stream
EXEC @hr = sp_oamethod @object, 'Open'
IF (@hr <> 0)
   RAISERROR('Failed to open the stream object', 11, 0)
-- write the buffer to the stream
EXEC @hr = sp_oamethod @object, 'Write', NULL, @Data
IF (@hr <> 0)
   RAISERROR('Failed to write a buffer to the stream', 11, 0)
-- save the stream to a file with overwrite
EXEC @hr = sp_oamethod @object, 'SaveToFile', NULL, @Path, 2
IF (@hr <> 0)
   RAISERROR('Failed to save the stream to a file', 11, 0)
-- cleanup
EXEC sp_oadestroy @object

请注意，通过此方法（以及我知道的SQL2000中的所有其他方法）访问文件系统将在SQL Server进程的安全上下文中进行，而不是存储过程的调用者。

Note that access to the file system via this method (and all others in SQL2000 that I know) will occur under the security context of the SQL Server process, not the caller of the stored procedure.

如果您不习惯将ADO组件加载到SQL Server进程中，可以考虑实现来编写文件。

If you aren't comfortable loading the ADO components into your SQL Server process, you could look into implementing an extended stored procedure to write your files.

这篇关于SQL Server 2000：如何将Image变量从存储过程保存到文件系统中的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！