Support - SQL Analytics Endpoint Feasibility for Pipeline Orchestration in Fabric

[Sureshmannem]

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

[v-mdharahman]

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]

Hi @v-mdharahman 

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!

[v-mdharahman · Accepted solution]

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.