Automated Profiler trace import

****DB CREATE SCRIPTS****
/****** Object:  Database ProfilerLog    Script Date: 04/03/2005 09:59:07 ******/IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ProfilerLog')
DROP DATABASE [ProfilerLog]

CREATE DATABASE [ProfilerLog]  ON (NAME = N'ProfilerLog_Data', FILENAME = N'D:\DB_Data\ProfilerLog_Data.MDF' , SIZE = 1024, FILEGROWTH = 10%) LOG ON (NAME = N'ProfilerLog_Log', FILENAME = N'D:\DB_Log\ProfilerLog_Log.LDF' , SIZE = 128, FILEGROWTH = 10%)
exec sp_dboption N'ProfilerLog', N'autoclose', N'false'
exec sp_dboption N'ProfilerLog', N'bulkcopy', N'false'
exec sp_dboption N'ProfilerLog', N'trunc. log', N'true'
exec sp_dboption N'ProfilerLog', N'torn page detection', N'true'
exec sp_dboption N'ProfilerLog', N'read only', N'false'
exec sp_dboption N'ProfilerLog', N'dbo use', N'false'
exec sp_dboption N'ProfilerLog', N'single', N'false'
exec sp_dboption N'ProfilerLog', N'autoshrink', N'false'
exec sp_dboption N'ProfilerLog', N'ANSI null default', N'false'
exec sp_dboption N'ProfilerLog', N'recursive triggers', N'false'
exec sp_dboption N'ProfilerLog', N'ANSI nulls', N'false'
exec sp_dboption N'ProfilerLog', N'concat null yields null', N'false'
exec sp_dboption N'ProfilerLog', N'cursor close on commit', N'false'
exec sp_dboption N'ProfilerLog', N'default to local cursor', N'false'
exec sp_dboption N'ProfilerLog', N'quoted identifier', N'false'
exec sp_dboption N'ProfilerLog', N'ANSI warnings', N'false'
exec sp_dboption N'ProfilerLog', N'auto create statistics', N'false'
exec sp_dboption N'ProfilerLog', N'auto update statistics', N'false'
if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'ProfilerLog', N'db chaining', N'false'

go

Use [ProfilerLog]

/****** Object:  Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 09:59:51 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sqltext_combined]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sqltext_combined]

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sqltext_combined]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[sqltext_combined] (
[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
[DateEntered] [datetime] NOT NULL CONSTRAINT [DF__sqltext_c__DateE__7C8480AE] DEFAULT (getdate()),
[EventClass] [int] NULL ,
[DatabaseID] [int] NULL ,
[TextData] [varchar] (6000) NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[EndTime] [datetime] NULL ,
[Reads] [bigint] NULL ,
[Writes] [bigint] NULL ,
[CPU] [int] NULL ,
[TransactionID] [bigint] NULL ,
[NTUserName] [varchar] (128) NULL ,
[NTDomainName] [varchar] (128) NULL ,
[HostName] [varchar] (128) NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [varchar] (128) NULL ,
[LoginName] [varchar] (128) NULL ,
[StartTime] [datetime] NULL ,
[Permissions] [int] NULL ,
[Severity] [int] NULL ,
[EventSubClass] [int] NULL ,
[ObjectID] [int] NULL ,
[Success] [int] NULL ,
[IndexID] [int] NULL ,
[IntegerData] [int] NULL ,
[ServerName] [varchar] (128) NULL ,
[ObjectType] [int] NULL ,
[NestLevel] [int] NULL ,
[State] [int] NULL ,
[Error] [int] NULL ,
[Mode] [int] NULL ,
[Handle] [int] NULL ,
[ObjectName] [varchar] (128) NULL ,
[DatabaseName] [varchar] (128) NULL ,
[FileName] [varchar] (128) NULL ,
[OwnerName] [varchar] (128) NULL ,
[RoleName] [varchar] (128) NULL ,
[TargetUserName] [varchar] (128) NULL ,
[DBUserName] [varchar] (128) NULL ,
[TargetLoginName] [varchar] (128) NULL ,
[ColumnPermissions] [int] NULL ,
 PRIMARY KEY  CLUSTERED 
(
[RowNumber]
)  ON [PRIMARY] 
) ON [PRIMARY]
END
/****** Object:  Index SQLTEXT_COMB_IX1 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:12 ******/if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX1' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX1]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX1] ON [dbo].[sqltext_combined]([RowNumber]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX2 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:17 ******/if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX2' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX2]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX2] ON [dbo].[sqltext_combined]([SPID], [Duration]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX3 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:20 ******/if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX3' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX3]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX3] ON [dbo].[sqltext_combined]([SPID], [CPU]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX4 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:26 ******/if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX4' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX4]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX4] ON [dbo].[sqltext_combined]([SPID], [Reads]) ON [PRIMARY]
GO
/****** Object:  Index SQLTEXT_COMB_IX5 on Table [dbo].[sqltext_combined]    Script Date: 04/03/2005 10:00:29 ******/if exists (select * from dbo.sysindexes where name = N'SQLTEXT_COMB_IX5' and id = object_id(N'[dbo].[sqltext_combined]'))
drop index [dbo].[sqltext_combined].[SQLTEXT_COMB_IX5]
GO

 CREATE  INDEX [SQLTEXT_COMB_IX5] ON [dbo].[sqltext_combined]([SPID], [Reads]) ON [PRIMARY]
GO

****END DB SCRIPTS*****
****SP Scritp****
if exists (select 'SP exists' from sysobjects where name = 'UP_AutoImport_TraceFiles' and xtype = 'p')
drop proc UP_AutoImport_TraceFiles
go
create procedure UP_AutoImport_TraceFiles
@TraceFilePath varchar(255)
--Create By:Regan Galbraith
--Create On:2004-12-28
--Purpose:
--This stored procedure was written to facilitate the automatic import of profiler trace files.
--
--Example:
--exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'
--
--Possible future additions:
--1> simple enhancement to specify DB to store data, and table.
--2>accepting a parameter instead of apply the default .trc. Use the .trc as default
--3>implementing default value's for dir's, so that it can run without parm's ... good or bad?
--
--Change Control:version 1 - creation and adding of comment
--
--
as
begin
set nocount on
set quoted_identifier off

create table #TraceFileList(TraceFileName varchar(128))

create table #DirExists
(FileExists int,
FileIsDir int,
ParentDirExists int)

declare @TraceFileName varchar(255)
declare @dirSQL varchar(255)
declare @ErrorDir varchar(6)

insert into #DirExists exec master..xp_fileexist @TraceFilePath
if not exists(select 'Dir Exists' from #DirExists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
begin
set @ErrorDir = 'Data'
goto DirError
end

set @dirSQL = 'dir '+@TraceFilePath+'*.trc /b'
--print @dirSQL

delete from #TraceFileList
insert into #TraceFileList
exec master..xp_cmdshell @dirSQL
--select * from #TraceFileList

declare TraceFileList cursor for select TraceFileName from #TraceFileList

open TraceFileList
fetch next from TraceFileList into @TraceFileName

while @@fetch_status <> -1
begin
-- this assumes a naming standard where your back names are databasename_ and then more detail. If you have 
-- a naming standard for backups of simply databasename.bak, then change this to charindex ('.' , for example.
select @TraceFileName = @TraceFilePath+@TraceFileName
select 'Files Processed : ' +@TraceFileName
if @TraceFileName is not null
begin
INSERT INTO [ProfilerLog].[dbo].[sqltext_combined]
([EventClass], [DatabaseID], [TextData], [SPID], [Duration], [EndTime], 
[Reads], [Writes], [CPU], [TransactionID], [NTUserName], [NTDomainName], 
[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime], 
[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], 
[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode], 
[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], 
[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions])
SELECT [EventClass],[DatabaseID],Cast([TextData] as varchar(6000)) as TextData,
[SPID],[Duration],[EndTime],[Reads],[Writes],[CPU], [TransactionID], [NTUserName], [NTDomainName],
[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime], 
[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID], 
[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode], 
[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName], 
[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions]

FROM ::fn_trace_gettable(@TraceFileName, default)
--where max(DATALENGTH(textdata)) <= 6000
end
    fetch next from TraceFileList into @TraceFileName
end

close TraceFileList
deallocate TraceFileList
goto finish

DirError:
Print 'An error with the '+@errordir+' Directory given - please check that it exists. Thanks'
drop table #TraceFileList
drop table #DirExists
RETURN -1

Finish:
drop table #TraceFileList
drop table #DirExists
RETURN 0

end

--exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'


****END SP Script****