I have a Synapse SQL Serverless query as follows

SELECT *
FROM  OPENROWSET
(
    BULK 'mytestpath/*/*',
    DATA_SOURCE = 'LocalDataLake',
    FORMAT = 'Parquet'
)
WITH(
Foo INT,
Bar INT
) X

The underlying Blob path may or may not exist/have contents depending on some external process.

If it does not contain any files the above throws an error

Content of directory on path 'mytestpath/*/*' cannot be listed.

How can I just get it to return an empty result set instead?

I have a Synapse SQL Serverless query as follows

SELECT *
FROM  OPENROWSET
(
    BULK 'mytestpath/*/*',
    DATA_SOURCE = 'LocalDataLake',
    FORMAT = 'Parquet'
)
WITH(
Foo INT,
Bar INT
) X

The underlying Blob path may or may not exist/have contents depending on some external process.

If it does not contain any files the above throws an error

Content of directory on path 'mytestpath/*/*' cannot be listed.

How can I just get it to return an empty result set instead?

• azure-synapse
• openrowset

1 Answer 1

This appears to be a side effect of using the syntax described in Read subset of files in folder using multiple file paths

On adding a second dummy path (that you should be sure definitely never exist) as below

SELECT *
FROM  OPENROWSET
(
    BULK ('mydummypaththatwillneverexist/*/*', 
          'mytestpath/*/*'),
    DATA_SOURCE = 'LocalDataLake',
    FORMAT = 'Parquet'
)
WITH(
Foo INT,
Bar INT
) X

the query now no longer throws an error and returns a zero row resultset.

The 13807 messages are now shown as warnings rather than statement aborting errors.