Fabric SQL Databases cross reference

[Salvor71]

I've created two SQL datadases, DB1 and DB2, and I'd like to cross-reference objects (i.e. views, functions...) in my queries but it doesn't work, even when I use the "extended" name needed to connect to the db's with SMSS. If I run in database DB2 the query "select * from [DB1-afb3707b-10e5-4eaf-a96f-ff5d3d465b9a].logging.LogLevel" I get the error "Reference to database and/or server name in 'DB1-afb3707b-10e5-4eaf-a96f-ff5d3d465b9a.logging.LogLevel' is not supported in this version of SQL Server".

Is it possible, or will it be?

P.S. I don't mean cross-querying through the SQL endpoint, but db to db like you can do with Sql Server.

[v-sgandrathi]

Hi @Salvor71,

I'm glad to hear your query has been clarified! To make it easier for others in the community to find this solution, please consider marking your response as the accepted solution and giving Kudos to the reply that helped you. Thank you for sharing your experience!
We truly appreciate your participation in the Microsoft Community Forum!

Thankyou

[Salvor71 · Accepted solution]

Hi @nilendraFabric ,

yes I tried and it doesn't work, but now I'm clearing my head on this.

For what I see, when you use the logical database name you are always referring to the SQL endpoint. Also, the only objects of the SQL database visible through the SQL endpoint are the tables: my logging.LogLevel view is defined in the database and I don't see it in the Views folder of the SQL endpoint, but I can create the same view in the Sql endpoint so that in a way solves the problem.

Anyway you cannot reference the SQL endpoint from its database counterpart: if I run the query

SELECT * FROM DB1.logging.LogLevel; 

in the database DB1 I get the same reference error. If I run it from the SQL endpoint of another database, it works.

So, in conclusion, cross-database reference is (currently?) only for SQL endpoint as stated in the docs, that's why I asked whether it will be supported in the future. We'll see.

[v-sgandrathi]

Hi @Salvor71,

Thanks for using Microsoft Fabric Community Forum.

Thankyou @nilendraFabric  and @NandanHegde  I appreciate your prompt response to the issue. The solution provided by the community members may resolve your issue.

Additionally, here are some key points that might help you:

Please verify the SQL Server version and the compatibility level, as these settings can impact the behavior of cross-database queries.
The error message indicates that your current SQL Server version may not support cross-database queries as intended. If your version has limitations on cross-database access, consider upgrading to a newer version that supports this feature.
When executing queries that reference objects across multiple databases (e.g., tables, views, functions), ensure the user running the query has the necessary permissions in both databases.

Here is the Microsoft documentation Link on cross database queries you can refer for your idea:
SQL database Overview (Preview) - Microsoft Fabric | Microsoft Learn

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Regards,
Sahasra.

[Salvor71]

Hi @v-sgandrathi,

unfortunately the SQL server version is not something you can manage in Fabric, but I understand that things are evolving quite rapidly. I was trying to replicate the architecture I use in many of my projects where I have a service database that offers common services to the other databases on the same instance, like logging, configuration, validation etc. I think I can still do it but in the Fabric way.

[NandanHegde]

Based on my understanding, cross database query is not supported in fabric SQL database as at the backend it is using Azure SQL database engine.

Note: the SQL analytics endpoint does support it

[Salvor71]

Hi @NandanHegde,

yes, but with some limitations.

Anyway I think Fabric SQL database is just what was missing in the Fabric environment.

[nilendraFabric]

Hello @Salvor71 

have you tried using the logical database name without GUIDs or workspace identifiers:

SELECT * FROM DB1.logging.LogLevel;