Sql-Server-2005

嘗試寫入文件時出現錯誤 -2146825284 / 0x800A0BBC

  • June 19, 2019

我有一個查詢，用於sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2從image列寫入磁碟。它因我認為與文件權限有關的錯誤而失敗。-2146825284 / 0x800A0BBC. @fPath將包含類似C:\exportdir\1\MI0033705\150916 Document1.pdf

導出位置C:\exportdir在 sql server 上，執行 SQL 服務的使用者帳戶可以完全控制該文件夾。我已經通過以使用者身份登錄（恰好是域管理員）進行了測試，並且可以手動寫入文件沒有問題。

對類似 SP 的呼叫工作正常（sp_OACreate、sp_OASetProperty 等），只有當我嘗試寫入文件時。

我應該查看其他一些權限或設置以使其正常工作嗎？

程式碼：

DECLARE @outPutPath varchar(100)
, @i bigint
, @init int
, @econtents varbinary(max) 
, @fPath varchar(max)  
, @folderPath  varchar(max) 
, @efolderName nvarchar(31)
, @efolderid nvarchar(250)
, @filename varchar(max)
, @ekey nvarchar(250)

--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id bigint identity(1,1), ekey  nvarchar(250) , esize int, [econtents] varbinary(max) )

INSERT INTO @Doctable([ekey], [esize],[econtents])
SELECT TOP 3 ekey, esize, econtents FROM eattachment ORDER BY newid();
--Select top 3 ekey, esize, econtents from eattachment
select * from @doctable

SELECT @i = 1

WHILE @i <= 3
BEGIN 
   SET @ekey = (SELECT ekey from @doctable where id = @i)
   SET @efolderid = (SELECT replace(replace(replace(RTRIM(LTRIM(STUFF(LEFT(@ekey,33),1,1,''))), CHAR(13), ''), CHAR(10), ''),char(9),'') from @doctable where id = @i)
   SET @filename = RTRIM(LTRIM(SUBSTRING(@ekey, 35, LEN(@ekey))))
   SET @efoldername =  (select top 1 efoldername
                                   from efolder 
                                   where efolderid 
                                   like @efolderid
                                   )

   SELECT @ekey  as ekey
   SELECT @efolderid  as folder
   SELECT @efoldername  as foldername
   select @filename  as filename

   SET @outPutPath = N'C:\exportdir'

   SELECT 
    @econtents = (select [econtents] from @doctable where id = @i),
    @fPath = @outPutPath + '\'+ CAST([id] as varchar(21)) + '\' + @efolderName + '\' + @filename, 
    @folderPath = @outPutPath + '\'+ CAST([id] as varchar(21))
   FROM @Doctable WHERE id = @i

   select @econtents  as econtents
   select @fpath  as fpath
   select @folderpath  as folderpath

DECLARE @hr int;   
EXECUTE @hr = sp_OACreate 'ADODB.Stream', @init OUTPUT
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d creating object (OACreate).', 16, 1, @hr)
   RETURN
END
EXECUTE @hr = sp_OASetProperty @init, 'Type', 1;
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d setting property (OASetProperty).', 16, 1, @hr)
   RETURN
END 

EXECUTE @hr = sp_OAMethod @init, 'Open';
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d opening method (OAMethod Open).', 16, 1, @hr)
   RETURN
END 
EXECUTE @hr = sp_OAMethod @init, 'Write', NULL, @econtents;
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d writing (OAMethod Write).', 16, 1, @hr) --THIS LINE IS WHERE THE ERROR OCCURS!
   RETURN
END 
EXECUTE @hr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d writing file (OAMethod SaveToFile).', 16, 1, @hr)
   RETURN
END  
EXECUTE @hr = sp_OAMethod @init, 'Close';
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d closing (OAMethod Close).', 16, 1, @hr)
   RETURN
END  
EXECUTE @hr = sp_OADestroy @init; 
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d destroying (OADestroy).', 16, 1, @hr)
   RETURN
END

print 'Document Generated at - '+  @fPath   

--Reset the variables for next use
SELECT @econtents = NULL  
, @init = NULL
, @fPath = NULL  
, @folderPath = NULL
, @hr = NULL
SET @i = @i + 1
END

電子附件表：

CREATE TABLE [dbo].[eAttachment](
   [eKey] [nvarchar](250) NOT NULL,
   [eSize] [int] NULL,
   [eContents] [image] NULL,
CONSTRAINT [ePKU_eAttachment] PRIMARY KEY CLUSTERED 
(
   [eKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

錯誤：

(3 row(s) affected)

(3 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 50000, Level 16, State 1, Line 79
Error -2146825284 writing file (OAMethod SaveToFile).

樣本數據

鑰匙：0 0000000000000000000000000179858 20180213114502 Filename.pdf

調整大小：183958

econtents：（ 0x7B35303146323335462D373546302D343936342D394137392D4145363335304242393138.... etc 這是一個很長的字元串）

這裡還有一張圖片，顯示了另一個範例的許多變數的目前值：

顯示一些結果的圖像

“最低限度完整、可驗證的範例”包括將您的程式碼縮減為重現或調試錯誤所需的最小可行命令集。

由於您在sp_OAMethod呼叫 時遇到問題，因此SaveToFile您不需要圍繞您擁有的表格等提供所有詳細資訊等。

我將您的程式碼提煉為：

DECLARE @hr int;   
DECLARE @init int;
DECLARE @fPath varchar(260);
DECLARE @econtents varbinary(100);

SET @fPath = 'C:\temp\test_ADODB.Stream.txt';
SET @econtents = CRYPT_GEN_RANDOM(100);

EXECUTE @hr = sp_OACreate 'ADODB.Stream', @init OUTPUT
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d creating object (OACreate).', 16, 1, @hr)
   RETURN
END
EXECUTE @hr = sp_OASetProperty @init, 'Type', 1;
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d setting property (OASetProperty).', 16, 1, @hr)
   RETURN
END 

EXECUTE @hr = sp_OAMethod @init, 'Open';
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d opening method (OAMethod Open).', 16, 1, @hr)
   RETURN
END 
EXECUTE @hr = sp_OAMethod @init, 'Write', NULL, @econtents;
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d writing (OAMethod Write).', 16, 1, @hr)
   RETURN
END 
EXECUTE @hr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d writing file (OAMethod SaveToFile).', 16, 1, @hr)
   RETURN
END  
EXECUTE @hr = sp_OAMethod @init, 'Close';
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d closing (OAMethod Close).', 16, 1, @hr)
   RETURN
END  
EXECUTE @hr = sp_OADestroy @init; 
IF @hr <> 0  
BEGIN  
   RAISERROR('Error %d destroying (OADestroy).', 16, 1, @hr)
   RETURN
END

print 'Document Generated at - '+  @fPath

我的 SQL Server 有權訪問該C:\TEMP文件夾，並且此程式碼實際上按預期工作。這使我相信在程式碼之前在sp_OA...程式碼中生成文件名存在問題。

對您的程式碼執行一些進一步的故障排除，我認為它可以正常工作，假設存在所需的文件夾，並且 SQL Server 可以訪問它們：

SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.efolder', N'U') IS NOT NULL
DROP TABLE dbo.efolder;
IF OBJECT_ID(N'dbo.eAttachment', N'U') IS NOT NULL
DROP TABLE dbo.eAttachment;

CREATE TABLE dbo.efolder
(
   efolderid nvarchar(250) NOT NULL
   , efoldername varchar(260) NOT NULL
);

INSERT INTO dbo.efolder (efolderid, efoldername)
VALUES ('11111111111111111111111111111111', 'efoldername')
   , ('22222222222222222222222222222222', 'efoldername')
   , ('33333333333333333333333333333333', 'efoldername');

CREATE TABLE [dbo].[eAttachment](
   [eKey] [nvarchar](250) NOT NULL,
   [eSize] [int] NULL,
   [eContents] [image] NULL,
CONSTRAINT [ePKU_eAttachment] PRIMARY KEY CLUSTERED 
(
   [eKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

INSERT INTO dbo.eAttachment (eKey, eSize, eContents)
VALUES ('11111111111111111111111111111111111111111111111111111111111111111111111111111111', 100, CRYPT_GEN_RANDOM(100))
   , ('22222222222222222222222222222222222222222222222222222222222222222222222222222222', 100, CRYPT_GEN_RANDOM(100))
   , ('33333333333333333333333333333333333333333333333333333333333333333333333333333333', 100, CRYPT_GEN_RANDOM(100));

DECLARE @outPutPath varchar(100)
, @i bigint
, @init int
, @econtents varbinary(1000) 
, @fPath varchar(max)  
, @folderPath  varchar(max) 
, @efolderName nvarchar(31)
, @efolderid nvarchar(250)
, @filename varchar(max)
, @ekey nvarchar(250)

--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id bigint identity(1,1), ekey  nvarchar(250) , esize int, [econtents] varbinary(max) )

INSERT INTO @Doctable([ekey], [esize],[econtents])
SELECT TOP 3 ekey, esize, econtents FROM eattachment ORDER BY newid();
--Select top 3 ekey, esize, econtents from eattachment
select * from @doctable

SELECT @i = 1

WHILE @i <= 3
BEGIN 
   PRINT N'Loop #' + CONVERT(nchar(1), @i);
   SET @ekey = (SELECT ekey from @doctable where id = @i)
   SET @efolderid = (SELECT replace(replace(replace(RTRIM(LTRIM(STUFF(LEFT(@ekey,33),1,1,''))), CHAR(13), ''), CHAR(10), ''),char(9),'') from @doctable where id = @i)
   SET @filename = RTRIM(LTRIM(SUBSTRING(@ekey, 35, LEN(@ekey))))
   SET @efoldername =  (select top 1 efoldername
                                   from efolder 
                                   where efolderid 
                                   like @efolderid
                                   )
   SELECT @efolderid as efolderid;
   SELECT @ekey  as ekey
   SELECT @efolderid  as folder
   SELECT @efoldername  as foldername
   select @filename  as filename

   SET @outPutPath = N'C:\temp'

   SELECT 
    @econtents = (select [econtents] from @doctable where id = @i),
    @fPath = @outPutPath + '\'+ CAST([id] as varchar(21)) + '\' + @efolderName + '\' + @filename, 
    @folderPath = @outPutPath + '\'+ CAST([id] as varchar(21))
   FROM @Doctable WHERE id = @i

   select @econtents  as econtents
   select @fpath  as fpath
   select @folderpath  as folderpath

   DECLARE @folderCheck varchar(1000);
   SET @folderCheck = 'mkdir ' + @folderPath + '\' + @efolderName;
   EXEC sys.xp_cmdshell @folderCheck;

   DECLARE @hr int;   
   PRINT N'EXECUTE @hr = sp_OACreate ''ADODB.Stream'', @init OUTPUT';
   EXECUTE @hr = sp_OACreate 'ADODB.Stream', @init OUTPUT
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d creating object (OACreate).', 16, 1, @hr)
       RETURN
   END
   PRINT N'EXECUTE @hr = sp_OASetProperty @init, ''Type'', 1;'
   EXECUTE @hr = sp_OASetProperty @init, 'Type', 1;
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d setting property (OASetProperty).', 16, 1, @hr)
       RETURN
   END 
   PRINT N'EXECUTE @hr = sp_OAMethod @init, ''Open'';';
   EXECUTE @hr = sp_OAMethod @init, 'Open';
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d opening method (OAMethod Open).', 16, 1, @hr)
       RETURN
   END 
   PRINT N'EXECUTE @hr = sp_OAMethod @init, ''Write'', NULL, @econtents;'
   EXECUTE @hr = sp_OAMethod @init, 'Write', NULL, @econtents;
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d writing (OAMethod Write).', 16, 1, @hr) --THIS LINE IS WHERE THE ERROR OCCURS!
       RETURN
   END 
   PRINT N'EXECUTE @hr = sp_OAMethod @init, ''SaveToFile'', NULL, @fPath, 2'
   EXECUTE @hr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d writing file (OAMethod SaveToFile).', 16, 1, @hr)
       RETURN
   END  
   PRINT N'EXECUTE @hr = sp_OAMethod @init, ''Close'';'
   EXECUTE @hr = sp_OAMethod @init, 'Close';
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d closing (OAMethod Close).', 16, 1, @hr)
       RETURN
   END  
   PRINT N'EXECUTE @hr = sp_OADestroy @init;'
   EXECUTE @hr = sp_OADestroy @init; 
   IF @hr <> 0  
   BEGIN  
       RAISERROR('Error %d destroying (OADestroy).', 16, 1, @hr)
       RETURN
   END

   print 'Document Generated at - '+  @fPath   

   --Reset the variables for next use
   SELECT @econtents = NULL  
   , @init = NULL
   , @fPath = NULL  
   , @folderPath = NULL
   , @hr = NULL;

   SET @i = @i + 1;
END

“消息”選項卡的輸出：

循環#1
執行@hr = sp_OACreate 'ADODB.Stream'，@init 輸出
執行@hr = sp_OASetProperty @init, '類型', 1;
執行@hr = sp_OAMethod @init, '打開';
執行@hr = sp_OAMethod @init, 'Write', NULL, @econtents;
執行@hr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2
執行@hr = sp_OAMethod @init, '關閉';
執行@hr = sp_OADestroy @init;
文件生成於 - C:\temp\1\efoldername\333333333333333333333333333333333333333333333
循環 #2
執行@hr = sp_OACreate 'ADODB.Stream'，@init 輸出
執行@hr = sp_OASetProperty @init, '類型', 1;
執行@hr = sp_OAMethod @init, '打開';
執行@hr = sp_OAMethod @init, 'Write', NULL, @econtents;
執行@hr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2
執行@hr = sp_OAMethod @init, '關閉';
執行@hr = sp_OADestroy @init;
文件生成於 - C:\temp\2\efoldername\111111111111111111111111111111111111111111111
循環#3
執行@hr = sp_OACreate 'ADODB.Stream'，@init 輸出
執行@hr = sp_OASetProperty @init, '類型', 1;
執行@hr = sp_OAMethod @init, '打開';
執行@hr = sp_OAMethod @init, 'Write', NULL, @econtents;
執行@hr = sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2
執行@hr = sp_OAMethod @init, '關閉';
執行@hr = sp_OADestroy @init;
文件生成於 - C:\temp\3\efoldername\222222222222222222222222222222222222222222222

僅供參考，您看到的錯誤編號是adErrWriteFile根據 Microsoft 的錯誤程式碼查找工具1，根據Microsoft Docs ADO 錯誤參考，它具有以下解釋：

寫入文件失敗。您可能已經關閉了一個文件，然後嘗試對其進行寫入，或者該文件可能已損壞。如果文件位於網路驅動器上，則瞬態網路狀況可能會阻止寫入網路驅動器。

1 - 是的，這被稱為“Exchange”錯誤查找工具，但它幾乎適用於所有 Microsoft 產品

引用自：https://dba.stackexchange.com/questions/199471

相關問答

Sql-Server

DML 操作的 SQL Server 並行性

  • February 22, 2022
檢視問答
Sql-Server

如何在不耗盡記憶體的情況下執行包含許多插入的大型腳本？

  • February 2, 2022
檢視問答
Sql-Server

標記為非活動的 SQL Server 複製訂閱

  • February 2, 2022
檢視問答
Sql-Server

將大量表移動到不同的文件組

  • January 28, 2022
檢視問答
Sql-Server-2005

碎片整理或備份+還原

  • January 14, 2022
檢視問答
Sql-Server-2005

解決關聯遮罩和 I/O 關聯遮罩之間的衝突？

  • January 8, 2022
檢視問答