Re: Log everything
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 02/15/05
- Next message: Brian Madden: "Re: What does it mean to "recover" a database?"
- Previous message: Hugo Kornelis: "Re: Log everything"
- In reply to: Agoston Bejo: "Re: Log everything"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Feb 2005 22:02:36 +1100
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
>>
>
>
- Next message: Brian Madden: "Re: What does it mean to "recover" a database?"
- Previous message: Hugo Kornelis: "Re: Log everything"
- In reply to: Agoston Bejo: "Re: Log everything"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
