RETURN --- !!!!!!!!! REMOVE ME !!!!!!!!!!!!
/****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 11/02/2011 12:02:13 AM */
/****************************************************/
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 10
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'L:\Data\Hist1\Profiler\Temp\MergePropertyAndBuilding', @maxfilesize, NULL
IF (@rc != 0) GOTO ERROR
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 33, 1, @on
EXEC sp_trace_setevent @TraceID, 33, 9, @on
EXEC sp_trace_setevent @TraceID, 33, 6, @on
EXEC sp_trace_setevent @TraceID, 33, 10, @on
EXEC sp_trace_setevent @TraceID, 33, 14, @on
EXEC sp_trace_setevent @TraceID, 33, 11, @on
EXEC sp_trace_setevent @TraceID, 33, 12, @on
EXEC sp_trace_setevent @TraceID, 67, 1, @on
EXEC sp_trace_setevent @TraceID, 67, 9, @on
EXEC sp_trace_setevent @TraceID, 67, 13, @on
EXEC sp_trace_setevent @TraceID, 67, 6, @on
EXEC sp_trace_setevent @TraceID, 67, 10, @on
EXEC sp_trace_setevent @TraceID, 67, 14, @on
EXEC sp_trace_setevent @TraceID, 67, 11, @on
EXEC sp_trace_setevent @TraceID, 67, 12, @on
EXEC sp_trace_setevent @TraceID, 162, 1, @on
EXEC sp_trace_setevent @TraceID, 162, 9, @on
EXEC sp_trace_setevent @TraceID, 162, 6, @on
EXEC sp_trace_setevent @TraceID, 162, 10, @on
EXEC sp_trace_setevent @TraceID, 162, 14, @on
EXEC sp_trace_setevent @TraceID, 162, 11, @on
EXEC sp_trace_setevent @TraceID, 162, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 15, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 1, @on
EXEC sp_trace_setevent @TraceID, 10, 9, @on
EXEC sp_trace_setevent @TraceID, 10, 17, @on
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 11, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 6, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 11, 1, @on
EXEC sp_trace_setevent @TraceID, 11, 9, @on
EXEC sp_trace_setevent @TraceID, 11, 6, @on
EXEC sp_trace_setevent @TraceID, 11, 10, @on
EXEC sp_trace_setevent @TraceID, 11, 14, @on
EXEC sp_trace_setevent @TraceID, 11, 11, @on
EXEC sp_trace_setevent @TraceID, 11, 12, @on
EXEC sp_trace_setevent @TraceID, 43, 15, @on
EXEC sp_trace_setevent @TraceID, 43, 1, @on
EXEC sp_trace_setevent @TraceID, 43, 9, @on
EXEC sp_trace_setevent @TraceID, 43, 10, @on
EXEC sp_trace_setevent @TraceID, 43, 11, @on
EXEC sp_trace_setevent @TraceID, 43, 12, @on
EXEC sp_trace_setevent @TraceID, 43, 13, @on
EXEC sp_trace_setevent @TraceID, 43, 6, @on
EXEC sp_trace_setevent @TraceID, 43, 14, @on
EXEC sp_trace_setevent @TraceID, 42, 1, @on
EXEC sp_trace_setevent @TraceID, 42, 9, @on
EXEC sp_trace_setevent @TraceID, 42, 6, @on
EXEC sp_trace_setevent @TraceID, 42, 10, @on
EXEC sp_trace_setevent @TraceID, 42, 14, @on
EXEC sp_trace_setevent @TraceID, 42, 11, @on
EXEC sp_trace_setevent @TraceID, 42, 12, @on
EXEC sp_trace_setevent @TraceID, 45, 16, @on
EXEC sp_trace_setevent @TraceID, 45, 1, @on
EXEC sp_trace_setevent @TraceID, 45, 9, @on
EXEC sp_trace_setevent @TraceID, 45, 17, @on
EXEC sp_trace_setevent @TraceID, 45, 10, @on
EXEC sp_trace_setevent @TraceID, 45, 18, @on
EXEC sp_trace_setevent @TraceID, 45, 11, @on
EXEC sp_trace_setevent @TraceID, 45, 12, @on
EXEC sp_trace_setevent @TraceID, 45, 13, @on
EXEC sp_trace_setevent @TraceID, 45, 6, @on
EXEC sp_trace_setevent @TraceID, 45, 14, @on
EXEC sp_trace_setevent @TraceID, 45, 15, @on
EXEC sp_trace_setevent @TraceID, 44, 1, @on
EXEC sp_trace_setevent @TraceID, 44, 9, @on
EXEC sp_trace_setevent @TraceID, 44, 10, @on
EXEC sp_trace_setevent @TraceID, 44, 11, @on
EXEC sp_trace_setevent @TraceID, 44, 12, @on
EXEC sp_trace_setevent @TraceID, 44, 6, @on
EXEC sp_trace_setevent @TraceID, 44, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 15, @on
EXEC sp_trace_setevent @TraceID, 12, 16, @on
EXEC sp_trace_setevent @TraceID, 12, 1, @on
EXEC sp_trace_setevent @TraceID, 12, 9, @on
EXEC sp_trace_setevent @TraceID, 12, 17, @on
EXEC sp_trace_setevent @TraceID, 12, 6, @on
EXEC sp_trace_setevent @TraceID, 12, 10, @on
EXEC sp_trace_setevent @TraceID, 12, 14, @on
EXEC sp_trace_setevent @TraceID, 12, 18, @on
EXEC sp_trace_setevent @TraceID, 12, 11, @on
EXEC sp_trace_setevent @TraceID, 12, 12, @on
EXEC sp_trace_setevent @TraceID, 12, 13, @on
EXEC sp_trace_setevent @TraceID, 13, 1, @on
EXEC sp_trace_setevent @TraceID, 13, 9, @on
EXEC sp_trace_setevent @TraceID, 13, 6, @on
EXEC sp_trace_setevent @TraceID, 13, 10, @on
EXEC sp_trace_setevent @TraceID, 13, 14, @on
EXEC sp_trace_setevent @TraceID, 13, 11, @on
EXEC sp_trace_setevent @TraceID, 13, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 1, @on
EXEC sp_trace_setevent @TraceID, 41, 9, @on
EXEC sp_trace_setevent @TraceID, 41, 17, @on
EXEC sp_trace_setevent @TraceID, 41, 10, @on
EXEC sp_trace_setevent @TraceID, 41, 18, @on
EXEC sp_trace_setevent @TraceID, 41, 11, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 6, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 40, 1, @on
EXEC sp_trace_setevent @TraceID, 40, 9, @on
EXEC sp_trace_setevent @TraceID, 40, 6, @on
EXEC sp_trace_setevent @TraceID, 40, 10, @on
EXEC sp_trace_setevent @TraceID, 40, 14, @on
EXEC sp_trace_setevent @TraceID, 40, 11, @on
EXEC sp_trace_setevent @TraceID, 40, 12, @on
-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
EXEC sp_trace_setfilter @TraceID, 10, 0, 6, N'MergePropertyAndBuilding%'
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 4bab5b77-09ec-484c-98a6-c4b6ba8627de'
EXEC sp_trace_setfilter @TraceID, 10, 0, 1, NULL
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT TraceID=@TraceID
GOTO finish
ERROR:
SELECT ErrorCode=@rc
finish:
GO
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = Util.dbo.StringConcat('/* ---- Path: ' + PATH + ' ---- */
-- /* Stop */ EXEC sp_trace_setstatus @traceid = ' + CAST(id AS VARCHAR) + ', @status = 0 /* Stop */
-- /* Start */ EXEC sp_trace_setstatus @traceid = ' + CAST(id AS VARCHAR) + ', @status = 1 /* Start */
-- /* Close */ EXEC sp_trace_setstatus @traceid = ' + CAST(id AS VARCHAR) + ', @status = 2 /* Close */', '
GO
')
FROM msdb.sys.traces (NOLOCK) WHERE ID > 1
EXEC Util.dbo.PrintLargeText @SQL
GO
-- TO IMPORT
RETURN --- !!!!!!!!! REMOVE ME !!!!!!!!!!!!
IF OBJECT_ID('tempdb..#temp')IS NOT NULL DROP TABLE #temp
SELECT e.name AS EVENT,CAST([Duration] /1000000.0 AS NUMERIC(12,3)) AS [Duration], [READS], [Writes], [CPU], [SPID], CAST([TextData] AS VARCHAR(MAX)) AS TextData, [StartTime], [EndTime]
INTO #temp
FROM FN_TRACE_GETTABLE('L:\Data\Hist1\Profiler\Temp\MergePropertyAndBuilding.trc', DEFAULT) t LEFT OUTER JOIN sys.trace_events e (NOLOCK) ON e.trace_event_id = t.EventClass
SELECT *, util.dbo.CastXML(TextData) AS TextXML FROM #temp
WHERE EVENT LIKE '%stmt%'
--AND Duration > 0
ORDER BY Starttime