Create trace to catch SQL Errors in SQL 2005 Workgroup

Question

Post reply

Create trace to catch SQL Errors in SQL 2005 Workgroup

paul-941374

Say Hey Kid

Points: 701
More actions
February 28, 2009 at 6:45 am

#206466

SQL 2005 Workgroup doesn't come with Profiler so it seems I need to manually create a procedure to catch SQL errors to a certain database. SQL trace is completely new so I'm not sure how to do go about this, but I can't even get a trace basic created so I'm struggling to even get started. Can anyone help.
declare @tracefile nvarchar(500) set @tracefile=N'c:\traceewtraceFile2'
declare @trace_id int
declare @maxsize bigint
set @maxsize =1
exec sp_trace_create @trace_id output,2,@tracefile ,@maxsize
Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070003(error not found).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I have a couple of comments:

1. You probably don't want to to create your trace files on the root of the C drive.

2. What account is the SQL Server service running under? That account will need read/write access to the trace file path.

3. What rights does the account you are trying to create the trace have? You need ALTER TRACE permissions in order to create a trace.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

paul-941374 Say Hey Kid Points: 701 More actions · Answer 2

Hi Jack, many thanks for your reply. The problem was a permissions thing. It seems I needed to create the folder the trace was being saved to manually. I guess SQL had permission to write files but not folders. Anyway it's creating a trace now using the code below. I've not managed to find an event type that relates specifically to SQL errors so I'm having to use SQL:BatchStarting which seems to show all SQL. If you have any suggestions on how to improve on the code below that would be much appreciated. Thanks.

declare @trace_id int

declare @tracefile nvarchar(500)

declare @maxsize bigint

declare @on bit

declare @current_num int

set @on = 1

set @current_num = 1

set @trace_id = (select top 1 (id + 1) as next from sys.traces order by id desc)

set @tracefile = N'c:\sqlTraces\trace' + cast(@trace_id as varchar)

set @maxsize = 10

--Creates the specified trace

exec sp_trace_create @trace_id output, 0, @tracefile , @maxsize -- stops when maxsize is reached

--Create event and choose columns

while(@current_num < 65)

begin

exec sp_trace_setevent @trace_id, 50, @current_num, @on

set @current_num = @current_num+1

end

--Starts the specified trace.

exec sp_trace_setstatus @trace_id, 1

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Here is a query that returns the events in the Errors and Warnings Category which I think will have what you are looking for.:

SELECT

TC.category_id,

TC.NAME AS category_name,

TE.trace_event_id,

TE.[name] AS column_name

FROM

sys.trace_events AS TE JOIN

sys.trace_categories AS TC

ON TE.category_id = TC.category_id

WHERE

TC.[name] = 'Errors and warnings'

Typically you would want the Exception and the User Error Message.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

paul-941374 Say Hey Kid Points: 701 More actions · Answer 4

Thanks Jack, the last post has been helpful. I've now managed to get a trace working which shows me parametized SQL queries from ASP pages, but it's still not much help as it doesn't show me the values of the parameters. This is what I get..

SELECT * FROM members WHERE email = @P1 AND password = @P2

Is there any way I can see these parameter values?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Try adding the Stored Procedures RPC:Started and RPC:Completed events. This may show the parameter values.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

paul-941374 Say Hey Kid Points: 701 More actions · Answer 6

Hurray! You're a star. I've finally got to see the parameters with the following event type.

exec sp_trace_setevent @trace_id, 11, @current_num, @on -- RPC:Starting - Occurs when an RPC has started.

Many thanks for your help.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

No problem.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question