Solved

query linked server msdb view returns results from local view

Posted on 2011-09-20
3
400 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 41
GRANT, REVOKE, DENY 4 20
BULK INSERT most recent CSV 19 22
add 1 to a field for 100 rows 11 24
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now