Re: Log everything
From: Agoston Bejo (gusz1_at_freemail.hu)
Date: 02/15/05
- Next message: Tibor Karaszi: "Re: A lot of sp_cursorfetch in profiler."
- Previous message: Ahmad Jalil Qarshi: "Re: Why Query Response is Slow?"
- In reply to: David Gugick: "Re: Log everything"
- Next in thread: Hugo Kornelis: "Re: Log everything"
- Reply: Hugo Kornelis: "Re: Log everything"
- Reply: Greg Linwood: "Re: Log everything"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Feb 2005 11:31:25 +0100
OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:#65ljozEFHA.1084@tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> > Hi,
> > I've been using SQL Server for a while, but I'm fairly new to this
> > area.
> > I would like to log every error that occurs in every stored procedure
> > that I call (actually from an ASP page, but I don't think this is
> > relevant). One of my stored procedures sometimes (as far as I can
> > determine, quite non-deterministically) fails, but from ASP all the
> > error description I get is this:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> > completed. Either enlist this session in a new transaction or the NULL
> > transaction.
> >
> > My stored proc. calls quite a lot other procedures, so it would be
> > good to be able to determine where exactly the error occurs during
> > executing. (And I wouldn't like to put "IF @@ERROR <> 0" blocks after
> > every rows in every stored procedure.)
> > So the best would be if I could see in a log which stored procedures
> > were called with which values and which procedure failed in which
> > row. Is it (or at least part of it) possible?
> >
> > Thx,
> > Agoston
>
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
- Next message: Tibor Karaszi: "Re: A lot of sp_cursorfetch in profiler."
- Previous message: Ahmad Jalil Qarshi: "Re: Why Query Response is Slow?"
- In reply to: David Gugick: "Re: Log everything"
- Next in thread: Hugo Kornelis: "Re: Log everything"
- Reply: Hugo Kornelis: "Re: Log everything"
- Reply: Greg Linwood: "Re: Log everything"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
