SQL Server Profielr

Question

Post reply

SQL Server Profielr

lianv 90648

Old Hand

Points: 392
More actions
February 8, 2011 at 6:52 am

#143244

Good day .
I need to import multiple SQL Server Trace files into an SQL Server database . Is there a way to do it ? Any ideas ?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Pouliot Philippe SSCommitted Points: 1912 More actions · Answer 1

YOu can try with Bulk Insert with loop or you can try with SSIS

avinashily Say Hey Kid Points: 661 More actions · Answer 2

one option is to take the profiler output to tables directly while creating a trace file

and if suppose you have taken the trace output to a .trc file you can do this by running the following query.

USE database_name

GO

SELECT * INTO table_name FROM ::fn_trace_gettable('c:\filename.trc', default)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Or in profiler

File -> Save as -> Trace Table

Clear Sky SQL
My Blog[/url]

lianv 90648 Old Hand Points: 392 More actions · Answer 4

Thank you for the feedback. I just like to know how this can be scripted and scheduled in a job ?

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 5

avinashily (2/8/2011)
one option is to take the profiler output to tables directly while creating a trace file
and if suppose you have taken the trace output to a .trc file you can do this by running the following query.
USE database_name
GO
SELECT * INTO table_name FROM ::fn_trace_gettable('c:\filename.trc', default)

I would not recommend outputing directly to a table from a trace. It's much safer on production systems to go to file.

But, I agree, using ::fn_trace_gettable is the way to automate this.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 6

lianv 90648 (2/8/2011)
Thank you for the feedback. I just like to know how this can be scripted and scheduled in a job ?

Yes, use the script supplied by avinashily above.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

lianv 90648 Old Hand Points: 392 More actions · Answer 7

Good Day. Thank you for the feedback. But I want import the data into the same table everyday and don't want to recreate a new table everytime I want to upload a trace file . Our production server is SQL Server 2005 and the Audit server is SQL Server 2008 and we were unable to upload from SL Server 2005 to SQL Server 2008 . Any ideas ?

lianv 90648 Old Hand Points: 392 More actions · Answer 8

Thank you for the feedback. I need to schedule the trace as a job . I looked in the script and could not find any reference to a database or a table . How could this be scheduled then ?

lianv 90648 Old Hand Points: 392 More actions · Answer 9

lianv 90648

Old Hand

Points: 392

February 9, 2011 at 1:03 am

#1284243

Thanks,

I tried Bulkinsert, it failed .

avinashily Say Hey Kid Points: 661 More actions · Answer 10

hi lianv,

you can use this query to load the data into same table again and again instead of creating a new table.

insert INTO table_name FROM ::fn_trace_gettable('c:\filename.trc', default)

but the thing is that the trace files used to load into the table must be taken from the same trace template.