query linked server msdb view returns results from local view

I am trying to query a msdb view on a linked server instance and the query returns results from the local msdb view. The same query works on databases when the linked server is not a named instance. Here's the query...

SELECT db_name([database_id])
  FROM [server\instancename].MSDB.sys.database_mirroring
  WHERE database_id = 8

Thanks for any insight or help on this.
B
rcttrmnAsked:
Who is Participating?
 
JestersGrindCommented:
In your linked server properties, under server options, make sure that Data Access is set to true.

You could also try using an open query.

SELECT *
  FROM OPENQUERY([server\instancename]), 'SELECT db_name([database_id])  FROM MSDB.sys.database_mirroring
  WHERE database_id = 8')

Greg

0
 
TempDBACommented:
Check for the linked server where is it pointing, I mean which server.
Can you also print the linked server status using

select * from sys.servers s
WHERE NAME = '<your linked server>'
0
 
rcttrmnAuthor Commented:
Open query worked but I'm still not sure why the other way wasn't working.

Thanks
B
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.