Inserting results of sp_WhoIsActive in a remote table on remote sql server machine

SQL Server Administration
1

Hi,

Thanks to Adam for making sp_whoIsActive.

Below is the query that we want to run from sql server 'SERVERA'; sp is at 'SERVERB' also the table 'tbl_whoisactive' is at 'SERVERB'

Below script is run

SELECT a.*
From openrowset('SQLOLEDB', 'SERVERB'; 'sa'; 'passdrow',
'exec SERVERB.master.dbo.Sp_whoisactive
@get_outer_command=1,
@output_column_list =
''[dd%][session_id][sql_command][sql_text][login_name][host_name][database_name]
[wait_info][blocking_session_id][blocked_session_count][percent_complete][cpu][used_memory]
[reads][writes][program_name][collection_time]''
,
@find_block_leaders=1,
@destination_table = ''SERVERB.master.dbo.tbl_whoisactive'';

''select [dd hh:mm:ss.mss],session_id,login_name,host_name,database_name,blocking_session_id,
blocked_session_count
from SERVERB.master.dbo.tbl_whoisactive where
collection_time >DATEADD(MINUTE, -3, getdate())'''') as a;

Message is given as

Unclosed quotation mark after the character string 'exec SERVERB.master.dbo.Sp_whoisactive

Please highlight what am I missing. Thanks.

2

remove one single quote at the end

collection_time >DATEADD(MINUTE, -3, getdate())''') as a;

3

Thanks for reply.

However; on removing last quote it is giving message as below.

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'select [dd hh:mm:ss.mss],session_id,login_name,host_name,database_name,blocking_session_id,
blocked_session_count
from serverb'.