SQL Server创建目录并提取BLOB至对应目录的问题求助

问题描述 #

需要从SQL Server表中提取BLOB数据，要求为表内每行数据在@outPutPath路径下创建独立文件夹，并将对应文件存入该文件夹。原本的文件提取代码可正常运行，但添加创建目录的逻辑（将@folderName加入文件路径）后，出现报错：

Msg 22048, Level 15, State 0, Line 60
Error executing extended stored procedure: Invalid Parameter

完整测试代码如下：

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO
--------------------------------------------------------------------------------------
drop table [dbo].[Document];

CREATE TABLE [dbo].[Document](
    [Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Folder_Name] [varchar](50) NULL,
    [Extension] [varchar](50) NULL,
    [FileName] [varchar](200) NULL,
    [Doc_Content] [varbinary](max) NULL   
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
----------------------------------------------------------------------------------
SET IDENTITY_INSERT [Document] ON
GO

--------------------------------------------------------------------------------------
delete from [dbo].[Document] where Doc_Num in (1,3,4);


Insert into Document (Doc_Num,Folder_Name,Extension,FileName,Doc_Content)
select 2 as Doc_Num,'Test2','JPG' AS Extension,'TestImage.jpg' as FileName,* 
from openrowset(bulk 'C:\temp\BICH\TestBLOB\Import\TestImage.jpg', SINGLE_BLOB)
as X
GO
Insert into Document (Doc_Num,Folder_Name,Extension,FileName,Doc_Content)
select 5 as Doc_Num,'Test5','JPG' AS Extension,'Angle2.png' as FileName,* 
from openrowset(bulk 'C:\temp\BICH\TestBLOB\Import\Angle2.png', SINGLE_BLOB)
as X
GO
Insert into Document (Doc_Num,Folder_Name,Extension,FileName,Doc_Content)
select 6 as Doc_Num,'Test6','JPG' AS Extension,'iLoveTrD.png' as FileName,* 
from openrowset(bulk 'C:\temp\BICH\TestBLOB\Import\iLoveTrD.png', SINGLE_BLOB)
as X
GO

--  select * from [Document]


-------------------------------------------------------------------
DECLARE @outPutPath varchar(50) = 'C:\temp\BICH\TestBLOB\Export'
, @i bigint
, @init int
, @data varbinary(max) 
, @fPath varchar(max)  
, @folderPath  varchar(max)
, @folderName varchar(max)

--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [Folder_Name] varchar(100) ,
[FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable([Doc_Num],[Folder_Name],[FileName],[Doc_Content])
Select [Doc_Num],[Folder_Name],[FileName],[Doc_Content] FROM  [dbo].[Document]

SELECT @i = COUNT(1) FROM @Doctable   

WHILE @i >= 1   

BEGIN    

SELECT 
    @data = [Doc_Content],
    @folderName = [Folder_Name],
--  @fPath = @outPutPath + '\' + [Doc_Num] +'_' + [FileName],
    @fPath = @outPutPath + '\' + @folderName + '\' + [Doc_Num] +'_' + [FileName],
    @folderPath = @outPutPath + '\' + @folderName
    
FROM @Doctable WHERE id = @i

EXEC master.dbo.xp_create_subdir @folderPath; -- Creating sub-directory
EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1;  
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources
print 'Document Generated at - '+  @fPath   

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

报错详情：

(3 rows affected)
Msg 22048, Level 15, State 0, Line 60
Error executing extended stored procedure: Invalid Parameter
Document Generated at - C:\temp\BICH\TestBLOB\Export\Test6\6_iLoveTrD.png
Msg 22048, Level 15, State 0, Line 60
Error executing extended stored procedure: Invalid Parameter
Document Generated at - C:\temp\BICH\TestBLOB\Export\Test5\5_Angle2.png
Msg 22048, Level 15, State 0, Line 60
Error executing extended stored procedure: Invalid Parameter
Document Generated at - C:\temp\BICH\TestBLOB\Export\Test2\2_TestImage.jpg

Completion time: 2023-03-03T10:57:59.0748541+01:00

排查与解决 #

报错来自xp_create_subdir执行时的参数问题，核心原因是路径变量使用varchar类型，而xp_create_subdir和ADODB.Stream的SaveToFile方法要求传入Unicode字符串（nvarchar类型），隐式类型转换可能导致参数解析失败。

修正方案：

1. 将所有涉及路径的变量改为nvarchar类型，确保符合存储过程和COM组件的参数要求；
2. 路径字符串添加N前缀，明确标记为Unicode字符串；
3. 增加对Folder_Name为空的判断，避免无效路径。

修正后的核心代码片段：

-------------------------------------------------------------------
DECLARE @outPutPath nvarchar(50) = N'C:\temp\BICH\TestBLOB\Export'
, @i bigint
, @init int
, @data varbinary(max) 
, @fPath nvarchar(max)  
, @folderPath  nvarchar(max)
, @folderName nvarchar(max)

--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  nvarchar(100) , [Folder_Name] nvarchar(100) ,
[FileName]  nvarchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable([Doc_Num],[Folder_Name],[FileName],[Doc_Content])
Select CAST([Doc_Num] AS nvarchar(100)),[Folder_Name],[FileName],[Doc_Content] FROM  [dbo].[Document]

SELECT @i = COUNT(1) FROM @Doctable   

WHILE @i >= 1   

BEGIN    

SELECT 
    @data = [Doc_Content],
    @folderName = [Folder_Name],
    @fPath = @outPutPath + N'\' + @folderName + N'\' + [Doc_Num] + N'_' + [FileName],
    @folderPath = @outPutPath + N'\' + @folderName
    
FROM @Doctable WHERE id = @i

-- 跳过空文件夹名称的情况，避免无效路径
IF @folderName IS NOT NULL AND @folderName <> ''
BEGIN
    EXEC master.dbo.xp_create_subdir @folderPath; -- Creating sub-directory
END

EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1;  
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources
print 'Document Generated at - '+  @fPath   

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

关键修正点说明 #

• 所有路径相关变量（@outPutPath、@fPath、@folderPath、@folderName）及临时表@Doctable中的路径字段均改为nvarchar类型；
• 路径拼接时使用N'\'替代'\'，确保字符串为Unicode格式；
• 增加对@folderName非空的判断，防止创建无效的空目录路径；
• 将Doc_Num显式转换为nvarchar类型，避免隐式转换可能带来的问题。

内容的提问来源于stack exchange，提问作者goldenbutter