copy Sql Trace into a table

Question

Post reply

copy Sql Trace into a table

sanra001

Mr or Mrs. 500

Points: 527
More actions
November 26, 2007 at 2:38 pm

#119223

Hi

I am trying to import sql trace info into a table. For example if I have a trace file created in c:\mytrace diretcory. Is there a way to get this into a table? I have looked at the following statement:

INSERT INTO tblTrace SELECT * FROM ::fn_trace_gettable("c:\mytrace\test.trc")

However is there a way to append to this table. Fo example if there is a new file created , will this pickup the new file and insert the info into the tblTrace table?

Any help is much appreciated.

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth.Fisher SSC-Insane Points: 20129 More actions · Answer 1

INSERT INTO tblTrace SELECT * FROM ::fn_trace_gettable("c:\mytrace\test.trc")

This statement should append the data in the test.trc into tblTrace each time you run it just like a normal insert. Now it will append the entire contents of the test.trc file, so if you have a running trace you may have to add a where condition to limit it to new data. Based on startdate or something similar.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

noeld SSC Guru Points: 96590 More actions · Answer 2

noeld

SSC Guru

Points: 96590

November 26, 2007 at 4:21 pm

#754330

If what you are after is to implement some sort of "rollover" file read, that logic is up to you. There is nothing built-in for that.

* Noel

sanra001 Mr or Mrs. 500 Points: 527 More actions · Answer 3

Thanks for the input.

I have the following

declare @rc int

EXEC @rc = prSQLSecurityAudit 'C:\Trace\LOG'

SELECT * FROM :: fn_trace_getinfo(default)

select * into Trace_Table from :: fn_trace_gettable('C:\Trace\LOG',default )

However when I run this again I get Table already exists. But I do not want to drop the table.

Also Each time the Trace procedure prSQLSecurityAudit Runs its creating a new file in the c:\trace\log directory. I want to copy the contents of this new file to the table and keep the old trace files in the table as well.

is there a better way to do this?

Thanks

Kenneth.Fisher SSC-Insane Points: 20129 More actions · Answer 4



declare @rc int

EXEC @rc = prSQLSecurityAudit 'C:\Trace\LOG'

SELECT * FROM :: fn_trace_getinfo(default)

-- INTO TableName trys to create a new table.

-- This inserts into an existing table

INSERT INTO Trace_Table

SELECT * FROM :: fn_trace_gettable('C:\Trace\LOG',default )

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

sanra001 Mr or Mrs. 500 Points: 527 More actions · Answer 5

Thanks,However when I run the job now it says

C:\MSSQL\LOG either does not exist or is not a recognizable trace file. Or there was an error opening the file.

The trace file is int his directory. Do we need to specify the trace file also in the file path?

Then how would we load new trace files each time we run this job?

Thanks