Help with Scripting SQL Server 2000 Profiler please?

Help with Scripting SQL Server 2000 Profiler please?

Post by Chris Crowe [MVP » Tue, 06 Aug 2002 11:41:48



I am wanting to use SQL Profiler to log all requests to a database and store
them in another database for later auditing of duration and CPU usage.

I can manually setup a SQL Profiler trace and have it write to a table. But
how do I script this so that I can say start this process by using a Stored
Procedure?

Is that the best way to start the trace? from the stored procedure?

I have used the [File]-[Script Trace] option but it only wants to write to a
file.

Here is part of that file:

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share



-- Client side File and Table cannot be scripted

-- Writing to a table is not supported through the SP's

What does the line above really mean? Why is it not supported? I suppose I
can use BULK IMPORT to import the file?

If I use the file how do I start the trace? What actually happens when the
script is run? I see it creates a file but the file remains 0 bytes long.

Any help would be appreciated

Chris Crowe

 
 
 

Help with Scripting SQL Server 2000 Profiler please?

Post by Gert E.R. Draper » Tue, 06 Aug 2002 11:53:54


In SQL 2000 SQL Trace (which is the server side of SQL Profiler, SQL
Profiler is just the tool) is not capable of tracing in to table, it is the
tool (SQL Profiler) which does this, with all the overhead associated with
it. (SQL Profiler first retrieves the records to the client and then uses
singleton INSERT statements to push them in to a trace table.)

Best solution is to trace to a trace file and use fn_trace_gettable like
this:

SELECT * FROM ::fn_trace_gettable(('c:\my_trace.trc', -1)
GO

Besides that this is the only way you can script it using TSQL it will be
significantly faster too.


Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2002 All rights reserved.



> I am wanting to use SQL Profiler to log all requests to a database and
store
> them in another database for later auditing of duration and CPU usage.

> I can manually setup a SQL Profiler trace and have it write to a table.
But
> how do I script this so that I can say start this process by using a
Stored
> Procedure?

> Is that the best way to start the trace? from the stored procedure?

> I have used the [File]-[Script Trace] option but it only wants to write to
a
> file.

> Here is part of that file:

> -- Please replace the text InsertFileNameHere, with an appropriate
> -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
> extension
> -- will be appended to the filename automatically. If you are writing from
> -- remote server to local drive, please use UNC path and make sure server
> has
> -- write access to your network share




> -- Client side File and Table cannot be scripted

> -- Writing to a table is not supported through the SP's

> What does the line above really mean? Why is it not supported? I suppose I
> can use BULK IMPORT to import the file?

> If I use the file how do I start the trace? What actually happens when the
> script is run? I see it creates a file but the file remains 0 bytes long.

> Any help would be appreciated

> Chris Crowe


 
 
 

Help with Scripting SQL Server 2000 Profiler please?

Post by Richard Waymire [MS » Tue, 06 Aug 2002 11:58:34


Look at the sp_procoption, the 'startup' option will allow you to mark your
stored proc to run when SQL Server starts.

--
Richard Waymire, MCSE, MCDBA

This posting is provided "AS IS" with no warranties, and confers no rights.


> I am wanting to use SQL Profiler to log all requests to a database and
store
> them in another database for later auditing of duration and CPU usage.

> I can manually setup a SQL Profiler trace and have it write to a table.
But
> how do I script this so that I can say start this process by using a
Stored
> Procedure?

> Is that the best way to start the trace? from the stored procedure?

> I have used the [File]-[Script Trace] option but it only wants to write to
a
> file.

> Here is part of that file:

> -- Please replace the text InsertFileNameHere, with an appropriate
> -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
> extension
> -- will be appended to the filename automatically. If you are writing from
> -- remote server to local drive, please use UNC path and make sure server
> has
> -- write access to your network share




> -- Client side File and Table cannot be scripted

> -- Writing to a table is not supported through the SP's

> What does the line above really mean? Why is it not supported? I suppose I
> can use BULK IMPORT to import the file?

> If I use the file how do I start the trace? What actually happens when the
> script is run? I see it creates a file but the file remains 0 bytes long.

> Any help would be appreciated

> Chris Crowe

 
 
 

Help with Scripting SQL Server 2000 Profiler please?

Post by Chris Crowe [MVP » Tue, 06 Aug 2002 14:03:40


I am still unsure about starting the trace. Do I just run the script that I
get from the SQL Profiler tool?

I did run it but it did not apear to actually log anything...  Although the
file did get created but stayed at 0 bytes.

The basics of the script are:





...

-- Set the Filters







-- Set the trace status to start

--

Chris Crowe [IIS MVP]
www.iisfaq.com



Quote:> Look at the sp_procoption, the 'startup' option will allow you to mark
your
> stored proc to run when SQL Server starts.

 
 
 

Help with Scripting SQL Server 2000 Profiler please?

Post by Gert E.R. Draper » Tue, 06 Aug 2002 17:26:23


Something like this:
/****************************************************/
/* Created by: SQL Profiler                         */
/* Date: 08/05/2002  01:22:16 AM         */
/****************************************************/

-- Create a Queue




-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share


NULL

-- Client side File and Table cannot be scripted

-- Set the events

























































-- Set the Filters



-- Set the trace status to start

-- display trace id for future references

goto finish

error:

finish:
go

-- rember the traceID

select * from ::fn_trace_gettable('c:\sqltrace.trc', default)


Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2002 All rights reserved.



> I am still unsure about starting the trace. Do I just run the script that
I
> get from the SQL Profiler tool?

> I did run it but it did not apear to actually log anything...  Although
the
> file did get created but stayed at 0 bytes.

> The basics of the script are:






> ...

> -- Set the Filters







> -- Set the trace status to start

> --

> Chris Crowe [IIS MVP]
> www.iisfaq.com



> > Look at the sp_procoption, the 'startup' option will allow you to mark
> your
> > stored proc to run when SQL Server starts.

 
 
 

Help with Scripting SQL Server 2000 Profiler please?

Post by Richard Waymire [MS » Tue, 06 Aug 2002 23:50:22


Yup, and to use the procoption you wrap the whole script in

Create proc myaudittrace
as
...

RETURN

and then mark 'myaudittrace' for startup.

--
Richard Waymire, MCSE, MCDBA

This posting is provided "AS IS" with no warranties, and confers no rights.


> Something like this:
> /****************************************************/
> /* Created by: SQL Profiler                         */
> /* Date: 08/05/2002  01:22:16 AM         */
> /****************************************************/

> -- Create a Queue




> -- Please replace the text InsertFileNameHere, with an appropriate
> -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
> extension
> -- will be appended to the filename automatically. If you are writing from
> -- remote server to local drive, please use UNC path and make sure server
> has
> -- write access to your network share


> NULL

> -- Client side File and Table cannot be scripted

> -- Set the events

























































> -- Set the Filters



> -- Set the trace status to start

> -- display trace id for future references

> goto finish

> error:

> finish:
> go

> -- rember the traceID

> select * from ::fn_trace_gettable('c:\sqltrace.trc', default)


> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2002 All rights reserved.



> > I am still unsure about starting the trace. Do I just run the script
that
> I
> > get from the SQL Profiler tool?

> > I did run it but it did not apear to actually log anything...  Although
> the
> > file did get created but stayed at 0 bytes.

> > The basics of the script are:






> > ...

> > -- Set the Filters







> > -- Set the trace status to start

> > --

> > Chris Crowe [IIS MVP]
> > www.iisfaq.com



> > > Look at the sp_procoption, the 'startup' option will allow you to mark
> > your
> > > stored proc to run when SQL Server starts.

 
 
 

1. SQL Profiler 2000 - A little help please

Hi to all the Gurus,

I am using Enterprise Manager 2000 on my Desktop to communicate with a
version 7 Server and database.

I attempted to run a Table Creation script I generated using EM. The
version 7 server didn't appear in the drop down list of servers and so
I typed it in.

I can't remember now if it provided me with a list of databases but I
thought it should run on the database that was open in EM.

When I ran the script it first posted a message about not being able
to connect to the database and then appeared to run the dropping and
recreation of Tables and Indexes etc.

Well, my tables still had data in them which indicated to me it didn't
run.

This morning, after reading something on here, I loaded the script
into Query Analyser and bingo it worked.

However, a little later someone noticed that another database was
missing all it's data. This other database had the same tables in it.
And the tables had a creation date close to the time when I attempted
to run my script via Profiler.

Can anyone give me a clue as to what went wrong?

Thanks MTB

2. Access 97 Database corrupts regularly (VB5 frontend) HELP!

3. SQL Server 2000 and SQL Profiler

4. Renaming server

5. Sql Profiler Sql Server 2000

6. FancyTooltips class

7. Please Help with SQL Script (SQL Server 7.0)

8. SQL Mail on SQL 2000, just upgraded 7

9. Running on SQL Server 2000 Script prepared for SQL Server 6.5

10. SQL Server 2000 Profiler shows ADO Execution time is greater than Query Analyzer

11. SQL Server 2000 SP2 Profiler TextData column message

12. Permissions to use SQL Server 2000 Profiler

13. Profiler Problem with SQL Server 2000 (PrepareSQL - 71)