Forum Discussion

Sureshmannem's avatar
Sureshmannem
Frequent Visitor
1 year ago
Solved

Support - SQL Analytics Endpoint Feasibility for Pipeline Orchestration in Fabric

Dear Team,

 

We’ve initiated the migration of our data solution orchestration from Azure Data Factory (ADF) to Microsoft Fabric.

Our solution relies heavily on SQL queries and stored procedure logic. I’ve noticed that the SQL Analytics Endpoint in Fabric supports executing both queries and stored procedures.

 

I’d like to explore the feasibility of using this endpoint to orchestrate pipelines—specifically, whether we can leverage the SQL queries and stored procedures created within the SQL Analytics Endpoint on Lakehouse as part of our orchestration logic.

 

Looking forward to your insights and guidance.

 

est regards,
Suresh

data engineering
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Sureshmannem,

    To your follow-up: yes, you can execute SQL queries and stored procedures from within a Notebook in Fabric, using the built-in %%sql magic command.

    * Running a SQL Query saved in the Lakehouse (via SQL Analytics Endpoint). If you’ve saved a query (for example, a view or just want to run a raw T-SQL statement), you can execute it like this:

    %%sql
    SELECT * FROM YourLakehouse.dbo.YourTable

    Or if you’ve created a view or saved query:

    %%sql
    SELECT * FROM dbo.YourSavedView

    Make sure the the Notebook must be attached to the Lakehouse you're querying, and the SQL Analytics Endpoint must be published.

    * For executing a stored procedure

    If you’ve defined a stored procedure in the SQL Endpoint (something like usp_MyProcedure), you can call it like this:

    %%sql
    EXEC dbo.usp_MyProcedure

    And If your stored procedure takes parameters:

    %%sql
    EXEC dbo.usp_MyProcedure @Param1 = 'Value1', @Param2 = 123

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Sureshmannem,

    Thanks for reaching out to the Microsoft fabric community forum.

    You are absolutely right that the SQL Analytics Endpoint in Fabric supports executing T-SQL queries and stored procedures. However, there’s an important points to be aware of when it comes to orchestration via pipelines.

    There are current Limitation like the Stored Procedure activity in Microsoft Fabric Data Pipelines currently supports only Azure SQL Database and Azure SQL Managed Instances as connection targets. This means that Lakehouse SQL Analytics Endpoints are not yet supported in the Stored Procedure activity dropdown or connection options.

    This happens because although the Lakehouse provides a SQL Analytics Endpoint that allows for T-SQL querying (including stored procedures), this endpoint is read-only and not integrated with the Stored Procedure activity in pipelines at this time. So, stored procedures defined in the Lakehouse cannot be directly invoked within a pipeline using the built-in Stored Procedure activity.

    Workarounds you can try:

    * Use a Notebook activity in your pipeline to run a script that connects to the Lakehouse SQL endpoint and executes the stored procedure. This gives you more flexibility and control.

    * If your stored procedure can be triggered via an API or external service, you can use a Web activity to call that endpoint.

    These approaches allow you to incorporate Lakehouse logic into your orchestration flow, even though direct support via the Stored Procedure activity is not yet available. You can also go through these resources for better understanding.

    How to use the Stored procedure activity - Microsoft Fabric | Microsoft Learn

    What is the SQL analytics endpoint for SQL database in Fabric? - Microsoft Fabric | Microsoft Learn

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

    • Sureshmannem's avatar
      Sureshmannem
      Frequent Visitor
      1 year ago

      Hi Anonymous 

       

      Thanks for your support — this will definitely help us reuse our existing code base more effectively.

      I have a quick question:
      Is it possible to run SQL queries that are saved in a Lakehouse using a SQL endpoint from within a notebook? If so, could you please share a simple example of how to execute:

      • A SQL query saved in the endpoint
      • A stored procedure

      Thanks in advance!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Sureshmannem,

        To your follow-up: yes, you can execute SQL queries and stored procedures from within a Notebook in Fabric, using the built-in %%sql magic command.

        * Running a SQL Query saved in the Lakehouse (via SQL Analytics Endpoint). If you’ve saved a query (for example, a view or just want to run a raw T-SQL statement), you can execute it like this:

        %%sql
        SELECT * FROM YourLakehouse.dbo.YourTable

        Or if you’ve created a view or saved query:

        %%sql
        SELECT * FROM dbo.YourSavedView

        Make sure the the Notebook must be attached to the Lakehouse you're querying, and the SQL Analytics Endpoint must be published.

        * For executing a stored procedure

        If you’ve defined a stored procedure in the SQL Endpoint (something like usp_MyProcedure), you can call it like this:

        %%sql
        EXEC dbo.usp_MyProcedure

        And If your stored procedure takes parameters:

        %%sql
        EXEC dbo.usp_MyProcedure @Param1 = 'Value1', @Param2 = 123

         

        If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

        Best Regards,
        Hammad.