2

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.

0

2 Answers 2

4

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.

0
0

Another occasion when db_name() can return null is when you are dealing with the resources database which has database_id = 32767

for example:

select distinct case when database_id = 32767 
                     then 'resourceDb' 
                      else db_name(database_id) 
                 end, count(*)
from sys.dm_os_buffer_descriptors b with(nolock)
group by database_id
order by 1 desc 

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.