August 23, 2010 at 9:48 am
Hi all!
I hope you can help me, this problem is a pain in the ***.
Situation: I want to create a small Report for my boss, so he can easily check if all databases are online.
So i have an administration server and there all other sql server are listed as linked servers.
I wrote a stored procedure which query the database state and other values from each linked server. The normal State ist ONLINE.. but if the database is a mirror database the status ist RESTORING. So it would be userful to know that the RESTORING database is actually a mirrored database.
So i wrote an addtional select to query which databases are mirrored. And here starts the pain.
When i query the sys.database_mirroring view directly i got the State of the Database in the Field mirroring_role_desc.
But when i query it from another server through linked server the value in the Field is NULL where there was MIRROR before when queried locally.
And its only with the State "MIRROR", because the STATE "PRINCIPAL" ist correctly queried on both ways. I am very confused, i hope you can help me.
Sorry for my bad english!
heres the code:
SELECT 'SERVERNAME',name,
compatibility_level,
collation_name,
user_access_desc,
recovery_model_desc,
page_verify_option_desc,
is_auto_close_on,
is_auto_shrink_on,
is_auto_create_stats_on,
state_desc,
mirroring_role_desc
from [SERVERNAME].master.sys.databases m right join [SERVERNAME].master.sys.database_mirroring d
ON m.database_id = d.database_id
August 24, 2010 at 1:25 am
Can no one help me?
August 24, 2010 at 2:29 am
This works fine for me. I was able to replicate your issue by removing the 'connect' user rights on the master db for the remote login specified in the linked server. You should try running the same query after impersonating the account which will be running the query.. ie. EXECUTE AS LOGIN = 'sa'
or specify your login in the local login area and map it to a remote login.
August 24, 2010 at 4:10 am
Yes it works!
Thank you very much!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply