query linked server msdb view returns results from local view

Posted on 2011-09-20
Last Modified: 2012-05-12
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.
Question by:rcttrmn
LVL 21

Accepted Solution

JestersGrind earned 500 total points
ID: 36570108
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.

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


LVL 25

Expert Comment

ID: 36572505
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>'

Author Closing Comment

ID: 36573853
Open query worked but I'm still not sure why the other way wasn't working.


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question