Unable to execute Stored Procedure in Lakehouse using Data Pipeline

[anon97242]

Greetings!

I have a workspace that contains both a Lakehouse and a Warehouse. The Lakehouse has some stored procedures in it that write some data to the Warehouse tables.

When using the WebUI I am attempting to call these Stored Procedures via a Data Pipeline however the "Connection" dropdown in the Stored Procedure Activity Box only allows for a Connection to the Warehouse, and the Lakehouse does not appear in the Connection Dropdown, so I am unable to access these Stored Procedures via the pipeline. 

Is there a reason for this?

Thanks!

Terrell

[v-prasare]

@anon97242 As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

@Jai-Rathinavel Thanks for your prompt response

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-prasare · Accepted solution]

Hi @anon97242 , 

As per the current documentation, the Stored Procedure activity in Fabric Data Pipelines supports only Azure SQL and Azure SQL Managed Instances . This means that Lakehouse SQL endpoints are not available in the connection dropdown for this activity.

https://learn.microsoft.com/en-us/fabric/data-factory/stored-procedure-activity

While the Lakehouse provides a SQL analytics endpoint that allows for T-SQL queries, this endpoint is not yet integrated with the Stored Procedure activity in Data Pipelines. Therefore, stored procedures defined in the Lakehouse cannot be directly invoked within a pipeline using this activity.

https://learn.microsoft.com/en-us/fabric/data-warehouse/get-started-lakehouse-sql-analytics-endpoint

As a workaround, try using a Notebook activity or a Web activity within your pipeline to execute the Lakehouse stored procedures. For instance, a Notebook activity can run a script that connects to the Lakehouse SQL endpoint and executes the desired stored procedure. Alternatively, a Web activity can call a REST API that triggers the stored procedure execution.

This is a limitation in the current implementation of Fabric Data Pipelines, and we appreciate your feedback. The product team is aware of this gap, and enhancements to support Lakehouse stored procedures directly within pipelines are being considered for future updates
meanwhile you can submit your detailed feedback and ideas via Microsoft's official feedback channels, such as the Microsoft Fabric Ideas.  Feedback submitted here is often reviewed by the product teams and can lead...

Thanks,

Prashanth Are

MS Fabric community support

[anon97242]

aaahhh okay that makes more sense! Thank you for the response @v-prasare  will give it a try using the notebook workaround.

[Jai-Rathinavel]

Hi @anon97242 , Currently executing stored procedures to perfrom CRUD operations on lakehouse tables are not supported. Please find the link to the documentation below

T-SQL Surface Area in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

In addition, please refer to the following forum topic where the community support team has officially confirmed this issue

Solved: Stored procedure exec in lakehouse - Microsoft Fabric Community

Thanks,

Jai Rathinavel | LinkedIn

[anon97242]

@Jai-Rathinavel  
@v-prasare 
Thanks for the response, unfortuntely the issue is not related to "Create Table" statements in stored procedure, but simply calling a lakehouse stored procedure from a data pipeline.

This statement is also not exactly accurate : Currently executing stored procedures to perfrom CRUD operations on lakehouse tables are not supported.

I have no issue executing the stored procedures manually via SQL endpoint, where they read from the lakehouse and update the warehouse. The issue only manifests itself when I attempt to use these in a data pipeline.

Thanks,
Terrell