Hi,
I am doing a tuning exercise on a SQL 2K5 instance, I am encountering
a problem when Profiler goes to create a table. I have profiled
Profiler and have extracted the following code
CREATE TABLE [dbo].[con1] ([RowNumber] int IDENTITY(0,1) PRIMARY KEY,
[EventClass] int NULL,[Duration] bigint NULL,,
[TextData] ntext NULL,[SPID] int NULL,[BinaryData] image NULL,[CPU]
int NULL,[Reads] bigint NULL,[Writes] bigint NULL,[ApplicationName]
nvarchar(128) NULL,[ClientProcessID] int NULL,[DatabaseID] int NULL,
[DatabaseName] nvarchar(128) NULL,[EventSequence] int NULL,
[EventSubClass] int NULL,[HostName] nvarchar(128) NULL,[IntegerData]
int NULL,[IsSystem] int NULL,[LoginName] nvarchar(128) NULL,[LoginSid]
image NULL,[NTDomainName] nvarchar(128) NULL,[NTUserName]
nvarchar(128) NULL,[RequestID] int NULL,[ServerName] nvarchar(128)
NULL,[SessionLoginName] nvarchar(128) NULL,[StartTime] datetime NULL,
[TransactionID] bigint NULL)
As you can see, after Duration (the 3rd column), there is a double
comma inserted which of course is erroring.
This occurs when defining a table to save to prior to running the
trace, and defining a table after running the trace.
I have tried the standard profiler which appears to work fine.
I also updated to SP2 CTP (this is a test server) because it appeared
to be an issue simular to this http://support.microsoft.com/?
kbid=925335.
A quick response would be appreciated as I am a contractor and I am on
tight timescales.
Cheers
Steve
Steve,
I don't know how to fix the error with profiler but I recommend you not do
that in the fist place. Tracing to a table can be a huge performance hit and
can skew your results. It is recommended that you trace directly to a file
using sp_trace_create and then use fn_trace_gettable to retrieve the data.
You can do something like this to put the trace results in a table:
SELECT * INTO YourTable FROM fn_trace_gettable(xxx)
If you already have a trace in profiler you can save it to a file and do the
same.
Andrew J. Kelly SQL MVP
"Steve L" <steve_lawrenson@.tiscali.co.uk> wrote in message
news:1170411376.241148.280690@.v33g2000cwv.googlegr oups.com...
> Hi,
> I am doing a tuning exercise on a SQL 2K5 instance, I am encountering
> a problem when Profiler goes to create a table. I have profiled
> Profiler and have extracted the following code
> CREATE TABLE [dbo].[con1] ([RowNumber] int IDENTITY(0,1) PRIMARY KEY,
> [EventClass] int NULL,[Duration] bigint NULL,,
> [TextData] ntext NULL,[SPID] int NULL,[BinaryData] image NULL,[CPU]
> int NULL,[Reads] bigint NULL,[Writes] bigint NULL,[ApplicationName]
> nvarchar(128) NULL,[ClientProcessID] int NULL,[DatabaseID] int NULL,
> [DatabaseName] nvarchar(128) NULL,[EventSequence] int NULL,
> [EventSubClass] int NULL,[HostName] nvarchar(128) NULL,[IntegerData]
> int NULL,[IsSystem] int NULL,[LoginName] nvarchar(128) NULL,[LoginSid]
> image NULL,[NTDomainName] nvarchar(128) NULL,[NTUserName]
> nvarchar(128) NULL,[RequestID] int NULL,[ServerName] nvarchar(128)
> NULL,[SessionLoginName] nvarchar(128) NULL,[StartTime] datetime NULL,
> [TransactionID] bigint NULL)
> As you can see, after Duration (the 3rd column), there is a double
> comma inserted which of course is erroring.
> This occurs when defining a table to save to prior to running the
> trace, and defining a table after running the trace.
> I have tried the standard profiler which appears to work fine.
> I also updated to SP2 CTP (this is a test server) because it appeared
> to be an issue simular to this http://support.microsoft.com/?
> kbid=925335.
> A quick response would be appreciated as I am a contractor and I am on
> tight timescales.
> Cheers
> Steve
>
|||On 2 Feb, 14:11, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Steve,
> I don't know how to fix the error with profiler but I recommend you not do
> that in the fist place. Tracing to a table can be a huge performance hit and
> can skew your results. It is recommended that you trace directly to a file
> using sp_trace_create and then use fn_trace_gettable to retrieve the data.
> You can do something like this to put the trace results in a table:
> SELECT * INTO YourTable FROM fn_trace_gettable(xxx)
> If you already have a trace in profiler you can save it to a file and do the
> same.
> --
> Andrew J. Kelly SQL MVP
> "Steve L" <steve_lawren...@.tiscali.co.uk> wrote in message
> news:1170411376.241148.280690@.v33g2000cwv.googlegr oups.com...
>
>
>
>
>
>
> - Show quoted text -
Hi Andrew
thanks for the response, I will use the method above as a workaround
and thanks for that information. It would be nice to know if this is a
recognised issue by MS and what their recommended workarounds are for
it.
Cheers
Steve L
No comments:
Post a Comment