Stored Procedure Activity getting failed

Anirudh Govardhanam 0 Reputation points
2024-04-22T11:45:55.77+00:00

Hi,

I have got to use couple of external tables(parquet) in dedicated sql pool. when I run stored procedures in dedicated sql pool using those external tables, its running successfully. But when I started running stored procedure using stored procedure activity in synapse, its failing with the below error message.

{

"errorCode": "2402",

"message": "Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 105221. Error Message: COPY statement input file schema discovery failed: Cannot process the file \"https://abc.dfs.core.windows.net/cur-data/Delta/Private/abc/abc_OnPrem/abc/abc/Year%3d2023/Month%3d12/part-00000-0eee8304-f0bf-45e8-916d-52ebc16508d1.c000.snappy.parquet\" because it does not exist or you don't have file access rights.",

"failureType": "UserError",

"target": "SQL pool stored procedure1",

"details": []

}

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,395 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sina Salam 3,801 Reputation points
    2024-04-22T13:49:59.3866667+00:00

    Hello Anirudh Govardhanam ,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    Problem

    Sequel to your questions, I understand that you are encountering an error when attempting to run a stored procedure using external tables (Parquet format) within Synapse Analytics. While the stored procedures run successfully within the dedicated SQL pool, it fails when executed via a stored procedure activity in Synapse.

    Scenarios

    The user is leveraging Synapse Analytics for their data processing needs, specifically utilizing external tables in Parquet format stored in Azure Data Lake Storage Gen2. They have successfully implemented stored procedures within the dedicated SQL pool to interact with these external tables. However, when attempting to automate the execution of these stored procedures using a stored procedure activity in Synapse, they encounter an error "2402".

    Solution

    This solution will address the nature of error and potential causes to provide a robust solution.

    Error Message:

    The error message indicates a failure in schema discovery for the input file, suggesting either non-existent files or permission issues.

    While providing solution for the error code, I will advise you to confirm and recheck your permission settings for accessing the storage account, authentication methods, network connectivity issues, URI encoding, and logging/monitoring configurations. Please, do it one after the other.

    Verify File Existence

    Now, by using Azure Storage Explorer or Azure Portal to check if the specified Parquet file exists in the provided Data Lake Storage Gen2 path. If the file is missing, review the file path in the error message and ensure it is accurate.

    You can use the below python code to check file existence in Azure Data Lake Storage Gen2: 

    # Python code snippet to check file existence in Azure Data Lake Storage Gen2
from azure.storage.filedatalake import DataLakeServiceClient
storage_account_name = "<storage_account_name>"
storage_account_key = "<storage_account_key>"
file_system_name = "<file_system_name>"
file_path = "<file_path>"
service_client = DataLakeServiceClient(account_url=f"https://{storage_account_name}.dfs.core.windows.net", credential=storage_account_key)
file_system_client = service_client.get_file_system_client(file_system=file_system_name)
file_client = file_system_client.get_file_client(file_path)
exists = file_client.exists()
if exists:
    print("File exists.")
else:
    print("File does not exist.")

    Finally

    By following the above steps and utilizing the provided code snippet for file existence verification, you can systematically troubleshoot and resolve the issues encountered when executing stored procedures using external tables in Synapse Analytics.

    If the file exists and the problem persists. Ensure you review all the listed areas that can be the root cause of the issue, also kindly post a new error code in the comment.

    The issue can be systematically diagnosed and resolved to enable successful execution of stored procedures using external tables in Synapse Analytics.

    References

    Kindly read more from the right side of this page:

    Accept Answer

    I hope this is helpful! Do not hesitate to let me know if you have any other questions.

    ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

    Best Regards,

    Sina Salam

  2. Smaran Thoomu 9,685 Reputation points Microsoft Vendor
    2024-04-23T11:00:27.5666667+00:00

    Hi @Anirudh Govardhanam

    Thanks for the question and using MS Q&A platform.

    As per the error, the synapse pipeline is unable to access the parquet file in the Gen2 directory.

    As your storage is firewall-protected you need to create MSI on the SQL end.

    Your scenario has been documented in the below URL. Please review this article and let us know if you have any further questions.

    https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/synapse-studio-error-while-trying-to-read-data-from-storage/ba-p/1511965

    A similar issue has been discussed here

    I hope this helps you resolve the issue. If you have any further questions or concerns, please do not hesitate to ask.

    0 comments