Log everything

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,
AgostonAgoston 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|||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
>|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.
>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> 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
>