SQL Server Linked Server Issue – DB_NAME(database_id) Returns Null

sql servert-sql

I am trying to execute below query :

SELECT DB_NAME(Database_id) FROM [linked_servername].MASTER.SYS.DATABASE_MIRRORING
WHERE database_id > 4 and mirroring_role = 1

But I am getting Result as NULL means database name is null.

Best Answer

The scope of DB_NAME() is limited to the local instance. It only applies to databases that exist on the server where you are running the query.

Instead of using DB_NAME(), you should join to sys.databases with four part naming, as in the following example:

SELECT DB.name 
FROM [linked_servername].master.sys.database_mirroring DM
    INNER JOIN [linked_servername].master.sys.databases DB ON DM.database_id = DB.database_id
WHERE DM.database_id > 4 and mirroring_role = 1;

I notice you have capitalized portions of the query including the master database, the sys schema, and the database_mirroring system table. These objects exist in lower case, so you should code them in lower case. For SQL Servers that have a case-sensitive collation, specifying the correct case is important for many reasons.

Related Question