drop proc usp_tracestart
go
drop proc usp_traceend
go

create proc usp_tracestart
    @spid         smallint,
    @host         varchar(256),
    @table        varchar(256),
    @filter       varchar(256),
    @queue_handle int OUT
as

--Declare the variables
declare
    @column_value int         --data column bitmask

--Set the column mask for the data columns to capture
set @column_value = 67108864|1|131072|32768|65536|4096

--Create a queue.
exec master.dbo.xp_trace_addnewqueue
    1000,                     --max_items
    5,                        --timeout
    95,                       --thread_boost
    90,                       --thread_reduce
    @column_value,            --required_columns
    @queue_handle out         --queue_handle

--Specify the event classes to trace.
exec master.dbo.xp_trace_seteventclassrequired
    @queue_handle,            --queue_handle
    12,                       --event_class -> SQL:BatchCompleted
    1                         --isrequired

--Set any filters.
exec master.dbo.xp_trace_setappfilter
    @queue_handle,            --queue_handle
    'MS SQL Query Analyzer%', --'include_only_applications'
    'SQL Server Profiler%'    --'exclude_applications'

exec master.dbo.xp_trace_setspidfilter
    @queue_handle,           --queue_handle
    @spid                    --spid

exec master.dbo.xp_trace_sethostfilter
    @queue_handle,           --queue_handle
    @host,                   --'include_hosts'
    NULL                     --'exclude_hosts'

exec master.dbo.xp_trace_settextfilter
    @queue_handle,           --queue_handle
    @filter,                 --'include_text'
    NULL                     --'exclude_text'

--Configure the queue to write to a table.
exec master.dbo.xp_trace_setqueuedestination
    @queue_handle,          --queue_handle
    4,                      --destination -> To Table
    1,                      --value -> enabled
    @host,                  --'server'
    @table                  --'object'

--Start the consumer.
exec master.dbo.xp_trace_startconsumer
    @queue_handle           --queue_handle
/*
--Save the queue definition as DemoQueue.
exec master.dbo.xp_trace_savequeuedefinition
    @queue_handle,          --queue_handle
    'Stats',                --'queue_name'
    1                       --is_shared

--Mark it for autostart
exec master.dbo.xp_trace_setqueueautostart
    'Stats',                --'queue_name'
    1                       --is_shared
*/
go


create proc usp_traceend
    @queue_handle int
as

--Destroy the trace
exec master.dbo.xp_trace_destroyqueue
    @queue_handle         --queue_handle
/*
exec master.dbo.xp_trace_deletequeuedefinition
    'Stats',              --'queue_name'
    1                     --is_shared
*/
go

set nocount on
go

drop table vctest
drop table #traces
drop table vcstats
go

create table vctest (pk varchar(6) not null primary key)
create table #traces (queue_handle int not null primary key)
go

declare
    @trace int

exec dba.dbo.usp_tracestart
    @spid = @@spid,
    @host = 'PAGE47',
    @table = 'dba.dbo.vcstats',
    @filter = '%insert%',
    @queue_handle = @trace OUT
insert #traces
select @trace
go

declare @i int
set @i = 1
while @i <= 1000
begin
    insert vctest values (convert(varchar(6),@i))
    set @i = @i + 1
end
go

declare @trace int
select  @trace = queue_handle
from    #traces
exec dba.dbo.usp_traceend @trace
delete  #traces
where   queue_handle = @trace
go

select * from dba.dbo.vcstats
go