error?

[SQLnewcommer]

I would like to insert information about errors that occur in SQL (when creating tables and inserting into tables) into a table. I would like to do this automatically, that is , every time there is an error , information about it should automatically be inserted into a table.

Any suggestions on where to start?

 

[druer]

I executed a command like Create Table (field1 , field2)

which obviously produces an error because there is no datatype assigned.

If I subsequently check the master..sysprocesses table
select * from master..sysprocesses

I see that there is a field called Stmt_End there with a value of -1, while all others have a 0 value.

Indicating to me that perhaps if you really need this functionality you could create a trigger on the master..sysprocesses table and look for a Stmt_End value of -1 to indicate something is wrong. You could get the SPID value, and then issue a dbcc inputbuffer(SPID#) command to get the text that was fired which led to the error. Not sure if you are even allowed to creat a trigger on teh sysprocesses table, or if that could end up leading to big problems, but I thought I'd at least mention it for you as a place to consider starting at.

 

[mrdenny]

That won't work. You can't create triggers on system tables (I've tried in the past).

You could setup an alert to monitor for the specific error that you are looking to capture data about, and have that alert start a job or do something.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[druer]

I was thinking doing that would cause a basket full, of cans of worms.

 

[thegeezza]

Morning,

An approach I like to use that you may find useful is a concept of wrapping functionailty into tasks.

I wrap blocks of SQL code into tasks, with each task defined in a task table. Depending on whether or not a task is sucessfull its' status is updated accoringly.
There is also a job table and naturally a job is made up of many tasks.
Now ideally each task is a single SQL statement becuase I believe @error only recalls the status of the last executed statement.
The cool thing about this setup is that if your job fails you can easily identify the failed task (which should assist in identifying the type of problem) and resume execution from that point.

Hope this is helpfull.

cheers,
Thegeezza