SELECT sp_WhoIsActive into Table-CodePudding

I'm trying to script sp_WhoIsActive into a table. The goal is feed the table with an Agent Job every 10 seconds.

I followed this guide and I tried to feed the table this way:

--Log activity into table.  
DECLARE @destination_table VARCHAR(4000) = 
  '[Monitoring].[dbo].[WhoIsActive] ' 
  
EXEC sp_WhoIsActive
  @get_plans = 1, 
  @get_transaction_info = 1, 
  @destination_table = @destination_table;

But as result I receive the error:

Warning: The join order has been enforced because a local join hint is used.
Msg 50000, Level 16, State 1, Procedure sp_WhoIsActive, Line 1111 [Batch Start Line 0]
Destination table not properly formatted.

On Google I found many guides talking about a solution that could help me execute a Stored Procedure into a temp table and from there I could create a table:

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
   'EXEC sp_WhoIsActive')

SELECT * FROM #MyTempTable

But this process too is failing with error:

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

I tried following Kendra Little blog but that too is not working.

In the end I scripted out the table manually:

CREATE TABLE [dbo].[WhoIsActive](
    [dd_hh_mm_ss_mss] [nvarchar](50) NOT NULL,
    [session_id] [tinyint] NOT NULL,
    [sql_text] [nvarchar](max) NOT NULL,
    [sql_command] [nvarchar](400) NOT NULL,
    [login_name] [nvarchar](50) NOT NULL,
    [wait_info] [nvarchar](50) NOT NULL,
    [tran_log_writes] [nvarchar](50) NOT NULL,
    [CPU] [smallint] NOT NULL,
    [tempdb_allocations] [smallint] NOT NULL,
    [tempdb_current] [smallint] NOT NULL,
    [blocking_session_id] [nvarchar](50) NOT NULL,
    [reads] [int] NOT NULL,
    [writes] [float] NOT NULL,
    [physical_reads] [tinyint] NOT NULL,
    [query_plan] [nvarchar](50) NOT NULL,
    [used_memory] [tinyint] NOT NULL,
    [status] [nvarchar](50) NOT NULL,
    [tran_start_time] [datetime2](7) NOT NULL,
    [implicit_tran] [nvarchar](50) NOT NULL,
    [open_tran_count] [tinyint] NOT NULL,
    [percent_complete] [nvarchar](50) NOT NULL,
    [host_name] [nvarchar](50) NOT NULL,
    [database_name] [nvarchar](50) NOT NULL,
    [program_name] [nvarchar](100) NOT NULL,
    [start_time] [datetime2](7) NOT NULL,
    [login_tine] [datetime2](7) NOT NULL,
    [request_id] [tinyint] NOT NULL,
    [collection_time] [datetime2](7) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

But that too is failing and I cannot feed the table with a job.

sp_WhoIsActive is so popular, I cannot believe I'm the only one trying to insert the results into a table.

CodePudding user response：

You need to create a table suitable to use with the output of the procedure, the schema of which can vary depending on the options you use.

SP_WhoIsActive will actually give you the create script, so to capture the default options just do

declare @definition varchar(max)
exec sp_WhoIsActive @return_schema = 1, @schema = @definition output
print @definition

This returns the appropriate T-SQL:

CREATE TABLE < table_name > (
    [dd hh:mm:ss.mss] VARCHAR(8000) NULL
    ,[session_id] SMALLINT NOT NULL
    ,[sql_text] XML NULL
    ,[login_name] NVARCHAR(128) NOT NULL
    ,[wait_info] NVARCHAR(4000) NULL
    ,[CPU] VARCHAR(30) NULL
    ,[tempdb_allocations] VARCHAR(30) NULL
    ,[tempdb_current] VARCHAR(30) NULL
    ,[blocking_session_id] SMALLINT NULL
    ,[reads] VARCHAR(30) NULL
    ,[writes] VARCHAR(30) NULL
    ,[physical_reads] VARCHAR(30) NULL
    ,[used_memory] VARCHAR(30) NULL
    ,[status] VARCHAR(30) NOT NULL
    ,[open_tran_count] VARCHAR(30) NULL
    ,[percent_complete] VARCHAR(30) NULL
    ,[host_name] NVARCHAR(128) NULL
    ,[database_name] NVARCHAR(128) NULL
    ,[program_name] NVARCHAR(128) NULL
    ,[start_time] DATETIME NOT NULL
    ,[login_time] DATETIME NULL
    ,[request_id] INT NULL
    ,[collection_time] DATETIME NOT NULL
    )

Edit and run with the required target table name, then you can run sp_whoisactive with the destination table option

exec sp_WhoIsActive @destination_table='Monitoring.dbo.WhoIsActive'

See the docs