USE Cleartrace
go
if object_id('Cleartrace..CT_AutoServerList') is not null
DROP TABLE CT_AutoServerList
CREATE TABLE CT_AutoServerList 
(id INT IDENTITY,
server_name sysname,
trace_folder VARCHAR(200),
trace_name VARCHAR(50),
is_active BIT,
insert_datetime DATETIME DEFAULT(GETDATE()),
update_datetime datetime 
)

SELECT * FROM CT_AutoServerList

INSERT INTO CT_AutoServerList
		( server_name ,
		trace_folder,
		trace_name,
		  is_active ,
		  insert_datetime 
		)
VALUES	( 'yourservernamehere', -- server_name - sysname
'e:\foldertoholdtraces',
'tracenamegoeshere',
 1 , -- is_active - bit
'2012-07-31 10:02:00' 
)


-----------------------------
--Cleartrace automated load--
--mdb 2012/08/01 1.00      --
-----------------------------
CREATE PROCEDURE ReadTrace.usp_AutomateTraceLoad
as
/*
--mdb 20120801 first version!

Purpose: Pull trace files from a variety of servers and use Cleartracecmd (Cleartrace Command Line) to 
automatically load into tables for processing, using a particular trace name and saving to a folder
on the "processing" server.  

Cleartrace can be found at www.scalesql.com/cleartrace/
and is written(?) & maintained by Bill Graziano.  It's his tool and I take no credit for it.  
It's indispensable for reading traces.  My code simply tries to automate it.

Instructions:
Download Cleartrace (full version, since we use Cleartracecmd.exe).
Run Cleartrace and choose a server/database.  It will create the DB and objects as needed.  
Run this script in that database.
Create a folder to hold the trace files
Add rows to the CTAutoServerList table

Goal: to run an import for each particular trace for a server, saving locally, and making available via cleartrace
Most of the code here is to guarantee that it gets processed once.
*/
DECLARE 
 @cleartrace_server sysname ,
 @cleartrace_database sysname,
 @archive_folder varchar(300),
 @executable_folder VARCHAR(300),
 @min INT, @max INT,
 @full_archive_folder_name varchar(300),
 @error INT,
 @cmd NVARCHAR(4000),
 @msg VARCHAR(1000)

if object_id('tempdb..#Error_Finder') is not null
    drop table #Error_Finder
create table #Error_Finder (listing nvarchar (255))

if object_id('tempdb..#File_Results') is not null
    drop table #File_Results
CREATE TABLE #File_Results (
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)

SET @cleartrace_server = 'cleartraceservernamehere'
SET @cleartrace_database = 'ClearTrace' 
SET @archive_folder = 'e:\trace_file_archive' --where to save trace files.
SET @executable_folder = 'e:\trace_file_archive' --where cleartracecmd.exe is.
SELECT @min = 1, @max = 0 --if no records at all, skip

--get rid of trailing slashes on the two fields, just in case
IF RIGHT(@archive_folder,1)='\' SET @archive_folder = LEFT(@archive_folder,LEN(@archive_folder)-1)
IF RIGHT(@executable_folder,1)='\' SET @executable_folder = LEFT(@executable_folder,LEN(@executable_folder)-1)

--Get a list of just the active servers/traces
--we could replace this with a ROW_NUMBER'd table, but we call it multiple places and I don't want more variables
--doing this so we only run against valid servers
DECLARE @server_list TABLE 
	(id INT IDENTITY,
	server_name sysname,
	trace_folder VARCHAR(200),
	trace_name VARCHAR(50))
INSERT INTO @server_list 
	SELECT server_name, 
			trace_folder, 
			trace_name 
	FROM CT_AutoServerList 
	WHERE is_active = 1

--loop through valid servers and grab trace files 
SELECT @min = MIN(id), @max = MAX(id) FROM @server_list
SELECT @min, @max
WHILE @min <= @max
BEGIN
	TRUNCATE TABLE #File_Results
	TRUNCATE TABLE #Error_Finder
	SELECT	@full_archive_folder_name = NULL,
			@error = 0,
			@cmd = NULL 

	------------------------------------------------------
	--Step 1: verify archive folder exists for that server
	------------------------------------------------------
	SELECT @full_archive_folder_name = @archive_folder + '\' + server_name 
	FROM @server_list WHERE id = @min

    --See if folder for server exists.  If not, try and create the folder.  If that fails, throw an error.
	INSERT INTO #File_Results
		(File_Exists, file_is_a_directory, parent_directory_exists)
	--verify it exists
	EXEC Master.dbo.xp_fileexist @full_archive_folder_name

	IF (SELECT TOP 1 File_is_a_Directory FROM #File_Results) = 0
	--if it does not, create it.  
	BEGIN
		SELECT @cmd = 'mkdir ' + @full_archive_folder_name
		INSERT #Error_Finder EXEC xp_cmdshell @cmd
		--throw errors if the create failed, as it'll import but not 
		SET @error = @@ERROR
		IF @error <> 0 OR 
			(SELECT COUNT(*) FROM #Error_Finder where 
				listing like '%error%' 
				OR listing LIKE '%not ready%' 
				OR listing LIKE '%not found%') > 0
		BEGIN
			SELECT @msg = '[Cleartrace] Error ' + CONVERT(VARCHAR,@error) + ' occurred during folder creation.'
				RAISERROR (@msg,16,1)
		END
	END 

	--------------------------------------
	--Step 2: import using ClearTraceCmd--
	--------------------------------------
	TRUNCATE TABLE #Error_Finder
	SET @cmd = NULL 
	SET @error = 0
	SELECT @cmd = 
		@executable_folder + '\ClearTraceCmd.exe' 
		+ ' /s ' + @cleartrace_server
		+ ' /d ' + @cleartrace_database
		+ ' /f ' + '\\' + server_name + '\' + REPLACE(trace_folder,':','$') + '\' --change e: to e$, if not using shares
		+ trace_name + '_*' --_* allows web1 and web2
		+ ' /group ' + server_name + '_' + trace_name 
		+ ' /archivedir ' + @full_archive_folder_name
	FROM @server_list WHERE id = @min

	INSERT #Error_Finder EXEC xp_cmdshell @cmd

	SET @error = @@ERROR
	IF @error <> 0 OR 
		(SELECT COUNT(*) FROM #Error_Finder where 
			listing like '%error%' 
			OR listing LIKE '%not ready%' 
			OR listing LIKE '%not found%') > 0
	BEGIN
		SELECT @msg = '[Cleartrace] Error ' + CONVERT(VARCHAR,@error) + ' occurred during cmdline import.'
			RAISERROR (@msg,16,1)
		SELECT * FROM #Error_Finder
	END

	SET @min = @min + 1
end



DROP TABLE #Error_Finder
DROP TABLE #File_Results
go