Import .trc files to database table

I've got a trace running at all times and it saves my traces into a number of .trc files, but how can I import the information into a table so I can get some value out of it?

Well first of all there's an awesome utility here (with whom I'm not affiliated in the slightest) called ClearTrace.

But sometimes I need more verbose reporting on my SQL traces, so I like to dump them into a table (e.g. "trace_table") and sort with my own queries.

Say you're at a place where you have a directory of .trc files on your server in C:\MyTraceFiles\.  Here's how you get them into your own table for easy manipulation:

SELECT * into trace_table FROM ::fn_trace_gettable('C:\MyTraceFiles\Trace_SERVER1_20110901195901.trc', default); insert INTO trace_table SELECT * FROM ::fn_trace_gettable('C:\MyTraceFiles\Trace_MyServer_20110901200002.trc', default); insert INTO trace_table SELECT * FROM ::fn_trace_gettable('C:\MyTraceFiles\Trace_MyServer_20110901200101.trc', default); ...

Update Table Based on Join to Different Database

Messed up an entire column of a large table today. Luckily it existed in another database on the same server. But how to get the data over? The syntax is tricky and I kept getting that the element "could not be bound". Here you go: use GOOD_DB UPDATE BAD SET BAD.[SHIPDATE]=GOOD.[SHIPDATE] FROM [BAD_DB].dbo.[BAD_TBL] BAD JOIN [SHIPDATE] GOOD -- You're in the "Good" database currently ON GOOD.OrderNumber = BAD.OrderNumber --Where "OrderNumber" is, hopefully, a PK or other selective column where a JOIN operation makes sense

Fixing orphaned SQL user accounts

Ever refresh/restore a database only to find out your database logins don't match up with the database user accounts? I run the following query to simultaneously identify any database user permissions that aren't mapped to current database logins and also synch up the passwords to the appropriate logins. Copying/pasting the output to a new query window should get permissions back the way they should be (assuming you want to set the password to "opensezME!": select 'sp_change_users_login ''auto_fix'', [' + name + '], NULL, ''opensezME!''' from sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and (len(sid) <= 16) and suser_sname(sid) is null order by name

Get statement from sqlhandle

I reverse-engineered a script I found on MSDN to get the actual SQL statement/sproc from a SQL handle. Note that the sqlhandle identifier is not passed as a literal.

-- http://msdn.microsoft.com/en-us/library/ms181929.aspx?PHPSESSID=ev0v34i8u80gkmn37sd8r65363 select sql_handle as Handle, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS Text from sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st where sql_handle = --0x04000D00E3572A56542E4601CE9E00010100001000000000