Solved

query linked server msdb view returns results from local view

Posted on 2011-09-20
3
406 Views
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.
B
0
Comment
Question by:rcttrmn
3 Comments
 
LVL 21

Accepted Solution

by:
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.

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

Greg

0
 
LVL 25

Expert Comment

by:TempDBA
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>'
0
 

Author Closing Comment

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

Thanks
B
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

726 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