Re: Log everything

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 03:40:47 -0500

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

Relevant Pages

  • Re: Log everything
    ... > I've been using SQL Server for a while, but I'm fairly new to this ... > I would like to log every error that occurs in every stored procedure ... you research creating a server-side trace. ...
    (microsoft.public.sqlserver.programming)
  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: ADO stored proc
    ... to know how to access a SQL Server stored procedure using vbscript ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)
  • Re: Stored Procedures vs DTS vs Jobs
    ... A stored procedure is complied code meaning that SQL Server has already ... > execution time, ...
    (microsoft.public.sqlserver.dts)