Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric Ideas just got better! New features, better search, and direct team engagement. Learn more

Add ability to query views, functions, and stored procedures in Fabric database from LH SQL endpoint

If I go into the SQL endpoint of a Lakehouse, I am able to query a Fabric database table directly, however I am unable to query a view or execute a stored procedure.

 

Please make this possible.

Status: New
Comments
Markpm_msft
Microsoft Employee

If I understand the ask, You have a Fabric SQL database, with views,functions,etc.. 
When you go into the SQL Analytics endpoint, you dont have access to these views/functions/procs in the Fabric SQL DB.

Do you have a (business) reason why you want the code constructs moved? Since these are two different engines with two different purposes.   


OldDogNewTricks
Advocate II
Here is my use case: I have a Fabric SQL metadata database where I store metadata for metadata driven pipelines. I have a stored procedure that will return the most recent date a stage was successfully run given a log id value (select max(DateTimeUTC) from db_metadata.dbo.RunLog where StageID = 241 and json_value(JSON_Message,'$.status') = 'Complete'). This data is then used within a stored procedure created on the SQL endpoint of a lakehouse in order to filter the data in the lakehouse for a paginated report that uses said stored procedure in the lakehouse SQL endpoint. Does that make sense?
Markpm_msft
Microsoft Employee

Since the SQL surface area is different ( json_value works) its impossible for us to guaranty the stored procs in the Fabric SQL DB will appear in the Fabric SQL AE. 
i.e. if we can move it without any issues, you should be able to deploy the common/shared functionality to both.

 

Since the SQLAE is a using delta tables as its store, point lookups will not be as fast as doing them on the SQL DB. 

What is not clear to me is the benefit you get from querying the SQL AE over the SQL DB.

OldDogNewTricks
Advocate II
The data resides in different places. My business and analytical data reside in the lakehouse. My metadata resides in Fabric SQL DB. I am accessing via SQL endpoint on lakehouse because that is the only place that I can create a stored procedure that can be used by paginated reporting where my business/analytical data can be accessed.