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'.

4

hi

hope this helps

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;
5

Thanks for reply.

It's giving below message. Seems issue with temporary table.

Msg 11526, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because statement 'INSERT #sessions
(
recursion,
session_id,
request_id,
session_number,
elapsed' in procedure 'sp_WhoIsActive' uses a temp table.

6

hi

please try this

SELECT a.*  
FROM OPENROWSET('SQLOLEDB', 'SERVERB'; 'sa'; 'passdrow',  
    '  
        BEGIN  
            CREATE TABLE #sessions (  
                recursion INT,  
                session_id SMALLINT,  
                request_id INT,  
                session_number SMALLINT,  
                elapsed TIME,  
                -- Include all necessary columns from sp_WhoIsActive  
                -- Add more columns as needed  
                sql_command NVARCHAR(MAX)  
            );  

            INSERT INTO #sessions (  
                recursion,  
                session_id,  
                request_id,  
                session_number,  
                elapsed,  
                sql_command  
            )  
            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   
                recursion,  
                session_id,  
                request_id,  
                session_number,  
                elapsed,  
                sql_command  
            FROM   
                #sessions;  

            DROP TABLE #sessions;  
        END  
    '  
) AS a;
7

Hi, thanks for revert; still showing message as below.

Msg 11525, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because statement 'INSERT INTO #sessions (
recursion,
session_id,' uses a temp table. Metadata discovery only supports temp tables when analyzing a single-statement batch.