How to query sys.dm_exec_requests on remote SQL SERVER 2019 instance.

Question

I am trying to run below query to find out/troubleshoot the queries taking longer; don't want to put load on production server hence running from 'stand alone SQL Server' to 'Production server'.

SELECT a.*

From openrowset('SQLOLEDB', 'PRODSERV'; 'sa'; 'PWD10l',

'SELECT a.,b. FROM PRODSERV.MASTER.sys.dm_exec_requests a

CROSS APPLY PRODSERV.MASTER.sys.dm_exec_sql_text(a.sql_handle) b

WHERE a.status <> ''background''

AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 180') as a

It gives below message on stand alone server.

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.".

Msg 8180, Level 16, State 1, Line 40

Statement(s) could not be prepared.

Msg 4122, Level 16, State 1, Line 42

Remote table-valued function calls are not allowed.

'Stand alone server' is having SQL server 2008 and Production server is having SQL Server 2019. Above select query works fine on production server if run from production server itself; want to make job on stand alone server that alerts if above threshold is crossed. Thanks in advance.

Answer 1

You cannot call table-valued function with four-part notation.

Now, why you would run OPENROWSET to run a distributed query from another server seems funny to me. I suspect that what you intended was this:

SELECT a.*
FROM OPENROWSET('MSOLEDBSQL', 'PRODSERV'; 'sa'; 'xxxxxx',
                'SELECT a.*,b.* FROM sys.dm_exec_requests a
                CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
                WHERE a.status <> ''background''
                AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 180') as a