SQL Server How to use a different file when the file does not exist in Synapse serverless SQL

ddhy6vgd 于 2023-05-16 发布在其他

关注(0)|答案(1)|浏览(124)

I want to use a query that uses another file if that file does not exist. I ran the following query in Synapse's Serverless SQL and got an error.

BEGIN TRY
    SELECT * 
    FROM 
        OPENROWSET(
        BULK 'https://dls001dev.dfs.core.windows.net/published/test/year=2023/month=05/test05.parquet',
        FORMAT = 'PARQUET'
    ) AS [result]
END TRY
BEGIN CATCH
    SELECT * 
    FROM 
        OPENROWSET(
        BULK 'https://dls001dev.dfs.core.windows.net/published/test/year=2023/month=04/test04.parquet',
        FORMAT = 'PARQUET'
    ) AS [result]
END CATCH

And error message is here.

File 'https://dls001dev.dfs.core.windows.net/published/test/year=2023/month=05/test05.parquet' cannot be opened because it does not exist or it is used by another process.

It is true that 'https://dls001dev.dfs.core.windows.net/published/test/year=2023/month=05/test05.parquet' does not exist, but instead of returning an error in this case, we would like to use 'https://dls001dev.dfs.core.windows.net/published/test/year=2023/month=04/test04.parquet' instead of returning an error in this case.

sql-server

来源：https://stackoverflow.com/questions/76214368/how-to-use-a-different-file-when-the-file-does-not-exist-in-synapse-serverless-s

1条答案

按热度按时间

2vuwiymt1#

As commented by @AlwaysLearning, using the query directly inside the try-catch block is giving error anyway. As suggested, using dynamic query EXEC or sp_executesql returned the required result.
I got the same error when I have tried the following similar query as yours:

Updating the query as follows helped in getting the desired result.

BEGIN TRY
    exec('SELECT * 
    FROM 
        OPENROWSET(
        BULK ''https://datalk100523.dfs.core.windows.net/data/sample2.csv'',
        FORMAT = ''CSV'',
        PARSER_VERSION = ''2.0'',
        HEADER_ROW = TRUE) AS [result]');
END TRY
BEGIN CATCH
exec('SELECT * 
    FROM 
        OPENROWSET(
        BULK ''https://datalk100523.dfs.core.windows.net/data/sample1.csv'',
        FORMAT = ''CSV'',
        PARSER_VERSION = ''2.0'',
        HEADER_ROW = TRUE) AS [result]');
END CATCH

The following query also returns the same result which uses sp_executesql .

BEGIN TRY
    DECLARE @SQLString NVARCHAR(500);  
SET @SQLString =  
     N'SELECT * 
    FROM 
        OPENROWSET(
        BULK ''https://datalk100523.dfs.core.windows.net/data/sample2.csv'',
        FORMAT = ''CSV'',
        PARSER_VERSION = ''2.0'',
        HEADER_ROW = TRUE) AS [result]';  
EXECUTE sp_executesql @SQLString;  
END TRY
BEGIN CATCH
SET @SQLString =  
     N'SELECT * 
    FROM 
        OPENROWSET(
        BULK ''https://datalk100523.dfs.core.windows.net/data/sample1.csv'',
        FORMAT = ''CSV'',
        PARSER_VERSION = ''2.0'',
        HEADER_ROW = TRUE) AS [result]';  
EXECUTE sp_executesql @SQLString;  
END CATCH

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server How to use a different file when the file does not exist in Synapse serverless SQL

1条答案

相关问题

热门标签

最新问答