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.server)
  • Re: XML vs SQL Server
    ... The built in factory assumes a common syntax among the ... So as long as the sql can be shared, ... procedures for Sql Server and stored procedure for Oracle? ... Oracle supports stored procedure overloads, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: XML vs SQL Server
    ... Then, yes, the built in factory will be sufficient. ... So as long as the sql can be shared, ... procedures for Sql Server and stored procedure for Oracle? ... Oracle supports stored procedure overloads, ...
    (microsoft.public.dotnet.languages.csharp)
  • 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)
Loading