0

I have an Azure SQL managed instance and would like to read pdf files(images) from Azure blob storage with openrowset. I'm able to read single file when I specify pdf file names but to read multiple files I'm getting an error.

code:

CREATE DATABASE SCOPED CREDENTIAL CREDENTIAL123
WITH IDENTITY ='SHARED ACCESS SIGNATURE',
SECRET='xxx'


CREATE EXTERNAL  DATA SOURCE DS
WITH (
    TYPE=BLOB_STORAGE,
    LOCATION='https://xxx.blob.core.windows.net/container-backup',
    CREDENTIAL = CREDENTIAL123
    );

Read single pdf image file:

SELECT BulkColumn FROM Openrowset(
                            Bulk 'doct_testing/3js-D-Report.pdf',
                            DATA_SOURCE ='DS',
                            Single_Blob) ImageData

Output: BulkColumn: 0x312457443823  

Read multiple pdf image file

SELECT BulkColumn FROM Openrowset(
                        Bulk 'doct_testing/*.pdf',
                        DATA_SOURCE ='DS',
                        Single_Blob
                            ) ImageData

Output:

Cannot bulk load. The file "https://xxx.blob.core.windows.net/container-backup/doct_testing/*.pdf" does not exist or you don't have file access rights.

SECRET is having FULL rights to read all the pdf files. If I specify the pdf names manually it's reading all the pdf files but when I use *.pdf it's throwing an error.

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11
user112359
  • 11
  • 2
  • 5

1 Answers1

0

Cannot bulk load. The file "https://xxx.blob.core.windows.net/container-backup/doct_testing/*.pdf" does not exist or you don't have file access rights.

The general cause of this error is due to insufficient permissions and file path is wrong or file in not exist on that particular location.

But you said you have all permission and files are existed there.

AFAIK, the use of wildcards, such as "*", in the file path when using the OPENROWSET function is not supported in Azure SQL Database.

You may read data from external data sources utilising a variety of providers, such as Azure Blob Storage, by using the OPENROWSET function in Azure SQL Database. Wildcard characters are not supported for pattern matching when used in the file path option, though it will consider *.pdf as a file name and giving you above error.

You would need to supply the list of files individually or the precise file path in order to read multiple files from Azure Blob Storage into Azure SQL Database using OPENROWSET. You would need to create separate OPENROWSET commands for each file if you wanted to load numerous files with different names.

Pratik Lad
  • 4,343
  • 2
  • 3
  • 11