Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sabotaged
Starting Member
2 Posts |
 Posted - 2006-08-20 : 21:38:23
|
| Is it possible to run a query to get a list of recent fatal errors, and their spid? (Specifically, fatal errors from stored procedures, from a job)I was searching the forums and came across a post " Some errors terminate the batch and there's npothing you can do about it.You can have an external client which can then obtain the eror from the connection but from within the connection you just have to live with the fact (and design for it) that you may get dumped without warning."Can anyone elaborate on how to do this? I'm using SQL Server 2005. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-20 : 23:38:24
|
| Dounds like something I wrote.It's valid for v2000 but for v2005 you can use a try catch block. That will catch most thingsIf you add this at the root level (outermost sp) then it's an easy way of adding error processing.begin tryinsert ...exec .........end trybegin catchinsert traceselect ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE() end catch==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sabotaged
Starting Member
2 Posts |
Posted - 2006-08-20 : 23:48:31
|
| It was my understanding that fatal errors - like if SQL Server loses an ODBC connection it's using - causes a SP to blow up in the middle, try / catch won't catch it. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-21 : 02:33:26
|
| That's true. It won't affect it until it tries to return the result at which time it's too late to log. There are also still errors that will abort the connection which cannot be trapped.For that you need to log at the client. For the lost connection you will just be able to detect the lost connection but if he error is on the server process you should get more info.On how to do that will depend on the client you are using.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Fatal error logging