MS SQL Server - fn_trace_gettable loses trace data

Author

fn_trace_gettable loses trace data - x64 bit trace to 32 bit table

peter.daniels@ihs.com

2006-11-29, 7:13 pm

I captured a sql trace on an x64 SQL 2005 SP1 instance:

Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition
(64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

via the sp_trace_x sprocs, and copied the trace file to my local 32 bit
(x86) computer:

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006
01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Then I used:

SELECT * INTO uia1
FROM ::fn_trace_gettable(
'<FilePath>', default)

to load it, but I got mostly gobbeldy gook - null data for most of the
columns I traced and many columns I didn't trace. I opened the file in
SQL Profiler and saved it to a table and everything looked fine.

Is this a known issue with fn_trace_gettable or am I a bonehead in the
way I'm using it? the BOL says nothing about what columns I should
use/not use. i really don't want to have to do the manual Profiler
thing to load the traces - I'm trying to automate baseline audting.

TIA,

-Peter

peter.daniels@ihs.com

2006-11-30, 7:14 pm

I am a bonehead and didn't look at the column names closely enough.
NON-ISSUE. sorry for the bad post.

peter.daniels@ihs.com wrote:
> I captured a sql trace on an x64 SQL 2005 SP1 instance:
>
> Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53
> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition
> (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> via the sp_trace_x sprocs, and copied the trace file to my local 32 bit
> (x86) computer:
>
> Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006
> 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Then I used:
>
> SELECT * INTO uia1
> FROM ::fn_trace_gettable(
'<FilePath>', default)
>
> to load it, but I got mostly gobbeldy gook - null data for most of the
> columns I traced and many columns I didn't trace. I opened the file in
> SQL Profiler and saved it to a table and everything looked fine.
>
> Is this a known issue with fn_trace_gettable or am I a bonehead in the
> way I'm using it? the BOL says nothing about what columns I should
> use/not use. i really don't want to have to do the manual Profiler
> thing to load the traces - I'm trying to automate baseline audting.
>
> TIA,
>
> -Peter

John Bell

2006-11-30, 7:14 pm

Hi Peter

I would expect trace files to be in the same format for both versions. Have
you tried this will a different/small file?

John

"peter.daniels@ihs.com" wrote:

> I captured a sql trace on an x64 SQL 2005 SP1 instance:
>
> Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53
> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition
> (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
>
> via the sp_trace_x sprocs, and copied the trace file to my local 32 bit
> (x86) computer:
>
> Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006
> 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Then I used:
>
> SELECT * INTO uia1
> FROM ::fn_trace_gettable(
'<FilePath>', default)
>
> to load it, but I got mostly gobbeldy gook - null data for most of the
> columns I traced and many columns I didn't trace. I opened the file in
> SQL Profiler and saved it to a table and everything looked fine.
>
> Is this a known issue with fn_trace_gettable or am I a bonehead in the
> way I'm using it? the BOL says nothing about what columns I should
> use/not use. i really don't want to have to do the manual Profiler
> thing to load the traces - I'm trying to automate baseline audting.
>
> TIA,
>
> -Peter
>
>

Sponsored Links