August 22, 2002 at 8:56 am
Using MS SQL Advanced Server 2000.
I used Profiler to create a trace that records when users login/logout using Crystal Reports. The problem with this trace is that when I close Profiler or log off, the trace quits.
So, using BOL and Inside SQL Server 2000 by Karen Delany, I created a script that I ran in Query Analyzer. The file is created, but there isn't any trace information. (Profiler always returns lots of records). Here's the script:
/* Program Name: crystaltrace.sql
** Programmer: William Fleming
** Date Created: 21 August 2002
** Last Updated: 22 August 2002
** Last Reviewed: 22 August 2002
** Purpose: To record information about users who log in to the database
** using Crystal Reports. Provides: application, login, domain name,
** start time, duration
** Dependancies: none
*/
DECLARE @TraceID int, @ON bit
SET @ON = 1
EXEC sp_trace_create @TraceID OUTPUT, 2, N'L:\Traces\CrystalTrace'
SELECT 'TraceID' = @TraceID
--Set what events need to be recorded
--Login with Application Name
EXEC sp_trace_setevent @TraceID, 14, 10, @ON
--Login with Login
EXEC sp_trace_setevent @TraceID, 14, 11, @ON
--Login with Domain Name
EXEC sp_trace_setevent @TraceID, 14, 7, @ON
--Login with Start Time
EXEC sp_trace_setevent @TraceID, 14, 14, @ON
--Login with Duration
EXEC sp_trace_setevent @TraceID, 14, 13, @ON
--Log Out with Application Name
EXEC sp_trace_setevent @TraceID, 15, 10, @ON
--Log Out with Login
EXEC sp_trace_setevent @TraceID, 15, 11, @ON
--Log Out with Domain Name
EXEC sp_trace_setevent @TraceID, 15, 7, @ON
--Log Out with Start Time
EXEC sp_trace_setevent @TraceID, 15, 14, @ON
--Log Out with Duration
EXEC sp_trace_setevent @TraceID, 15, 13, @ON
--Set a filter to only get Seagate Crystal Reports
EXEC sp_trace_setfilter @TraceID, 10, 1, 6, N'%Crystal'
--EXEC sp_trace_setfilter @TraceID, 10, 1, 6, N'Crystal Reports'
--Start the trace
EXEC sp_trace_setstatus @TraceID, 1
Any ideas as to what I'm doing wrong or why this script won't return any trace information?
BTW- soon as I run this script, I open up Crystal Reports and run a report. So, I should get at least my login data; but I'm not getting anything.
-Bill
August 23, 2002 at 4:42 am
For you to get the output to occurr you need to have the TraceID, I usually have written to a table with a description when I doing this. Then when you want to see the output you have to stop the trace like so.
EXEC sp_trace_setstatus TraceIDHere, 0
EXEC sp_trace_setstatus TraceIDHere, 2
Other than this you have the rollover setup so once a file will be 5 megs it should appear and rollover to a new file. But if under 5MB unless you change the max file size it will be in memory and to dump you have to stop and close the trace.
I believe that is all that is wrong here.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 23, 2002 at 6:04 am
Thanks, I will try stopping it and see if that gives me the data. Using Profiler didn't require me to stop the trace, as the application was used I saw a new entry right away.
By the way, I am using the TraceID. I set it so it becomes a variable (@TraceID).
-Bill
August 23, 2002 at 7:45 am
Thanks again. Stopped and closed the trace. Then I could view the data (after I adjusted my filter, it's not working with the LIKE %Crystal).
-Bill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply