如何在OpenRowSet中配置bulk_options从Azure Blob存储批量读取PDF文件？

问题原因 #

OPENROWSET(BULK) 语法本身不支持直接使用通配符批量读取Azure Blob存储中的文件，它仅能识别单个具体文件名。这就是你用*.pdf时报错的根本原因——即使权限完全正常，该语法也无法解析通配符路径。

解决方案：动态SQL批量读取 #

通过查询Blob存储的文件列表，动态生成读取每个PDF文件的OPENROWSET语句，最终合并所有结果，步骤如下：

1. 启用OLE自动化（若未启用） #

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

2. 创建存储过程生成动态SQL并执行 #

以下存储过程会遍历指定Blob路径下的所有PDF文件，生成对应读取语句并执行，返回所有文件的二进制内容：

CREATE PROCEDURE GetAllPdfFromBlob
AS
BEGIN
    DECLARE @FileList TABLE (FileName NVARCHAR(255))
    DECLARE @BlobUrl NVARCHAR(500) = 'https://xxx.blob.core.windows.net/container-backup/doct_testing/'
    DECLARE @SasToken NVARCHAR(500) = 'xxx' -- 替换为你的SAS令牌
    DECLARE @XmlResponse XML
    DECLARE @HttpObj INT
    DECLARE @ResponseText NVARCHAR(MAX)

    -- 调用OLE自动化获取Blob文件列表
    EXEC sp_OACreate 'MSXML2.XMLHTTP', @HttpObj OUT
    EXEC sp_OAMethod @HttpObj, 'open', NULL, 'GET', @BlobUrl + '?restype=container&comp=list&' + @SasToken, 'false'
    EXEC sp_OAMethod @HttpObj, 'send'
    EXEC sp_OAGetProperty @HttpObj, 'responseText', @ResponseText OUT
    EXEC sp_OADestroy @HttpObj

    -- 解析XML提取PDF文件名
    SET @XmlResponse = CAST(@ResponseText AS XML)
    INSERT INTO @FileList (FileName)
    SELECT 
        x.value('.', 'NVARCHAR(255)') AS FileName
    FROM @XmlResponse.nodes('//Blobs/Blob/Name') AS Files(x)
    WHERE x.value('.', 'NVARCHAR(255)') LIKE 'doct_testing/%.pdf'

    -- 生成动态SQL并执行
    DECLARE @DynamicSql NVARCHAR(MAX) = ''
    SELECT @DynamicSql = @DynamicSql + 
        'SELECT BulkColumn AS FileContent, ''' + FileName + ''' AS FileName FROM OPENROWSET(BULK ''' + FileName + ''', DATA_SOURCE=''DS'', SINGLE_BLOB) AS ImageData UNION ALL '
    FROM @FileList

    -- 移除末尾多余的UNION ALL
    SET @DynamicSql = LEFT(@DynamicSql, LEN(@DynamicSql) - 10)

    EXEC sp_executesql @DynamicSql
END

3. 执行存储过程获取所有PDF内容 #

EXEC GetAllPdfFromBlob

注意事项 #

• 确保SQL托管实例防火墙允许访问Azure Blob存储（默认允许，若有严格规则需调整）。
• SAS令牌需包含List和Read权限，才能获取文件列表并读取内容。
• 若文件数量过多导致动态SQL超长，可改用游标逐文件读取或分批处理。

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