Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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
0
rcttrmn
Asked:
rcttrmn
1 Solution
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now