I'm trying to query information on a linked server. The code works fine when run on the machine but doesn't when connected via a linked server: The error I get is: Purpose behind it is to keep a track of queries run across an estate of servers into 1 central so we can monitor impacts (basically building an in-house server

Is there a way to obtain linked server query information?

I’m trying to query information on a linked server. The code works fine when run on the machine but doesn’t when connected via a linked server:

SELECT
    r.session_id,
    r.start_time,
    r.total_elapsed_time,
    s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
    st.text
FROM
    [linked_server].[master].sys.dm_exec_requests r
INNER JOIN
    [linked_server].[master].sys.dm_exec_sessions s
ON
    r.session_id = s.session_id
LEFT JOIN
    [linked_server].[master].sys.dm_exec_connections c
ON
    r.session_id = c.session_id
OUTER APPLY
    [linked_server].[master].sys.dm_exec_sql_text(r.sql_handle) st where r.session_id  > 50

The error I get is:

Remote table-valued function calls are not allowed

Purpose behind it is to keep a track of queries run across an estate of servers into 1 central so we can monitor impacts (basically building an in-house server monitoring tool). Is there a setting I need to change on the linked server?

Answer

As per this question, you can either try adding a WITH (NOLOCK), or use OPENQUERY.

Advertisement

Answer