DBCC TRACE STORED PROCEDURE ERRORS

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC TRACE STORED PROCEDURE ERRORS

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-02-16 : 13:10:42

How can I set up a trace to record a stored procedure error in the log and alert me if it occures.

Example the schma changes and the stored procedure does not reflect the schema...

Server: Msg 213, Level 16, State 4, Procedure usp_force_error_test, Line 3
Insert Error: Column name or number of supplied values does not match table definition.

example:
set nocount on

create table MyTargetTable(a bit, b bit)
go
create table MySourceTable(a bit, b bit)
go
insert into MySourceTable(a, b)
select 1, 1
union select 1, 2
union select 1, 3
go
create procedure usp_force_error_test
as
insert into MyTargetTable
select * from MySourceTable
go
exec usp_force_error_test
go
truncate table MyTargetTable
go
alter table MySourceTable drop column b
go
exec usp_force_error_test
go
drop table MyTargetTable
drop table MySourceTable
drop procedure usp_force_error_test

You can do anything at www.zombo.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 16:47:13

Something like this?

declare @items int

select	@items = count(*)
from	information_schema.columns
where	table_name = 'sourcetable'
	and column_name in ('a', 'b')

If @items = 2
--do some inserting
else
--log error since there are not two columns anymore!

Peter Larsson
Helsingborg, Sweden

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-03-14 : 16:52:44

Actually it has more to do with migrating changes into production and being able to monitor if users are experiencing problems with any stored procedures that did not get caught in the QA process.

This way when we do large complex migrations I can monitor the environment in a proactive mannor rather than reacting to a user reported issue.

You can do anything at www.zombo.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:01:19

Something like this?

create procedure usp_force_error_test
as

-- start with debugging department
if (select debug from MainTable) = 1
begin
declare @items int

select @items = count(*)
from information_schema.columns
where table_name = 'sourcetable'
and
(column_name = 'a' and data_type = 'bit'
or column_name = 'b' and data_type = 'bit')

If @items <> 2
begin
insert into SomeLogTable VALUES ('Error here...')

return
end
end

-- do normal stuff here
insert MyTargetTable (a, b)
select a, b from MySourceTable

Peter Larsson
Helsingborg, Sweden

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-03-14 : 17:44:49

Your thinking about the problem from a schema perspective, which I can appreciate.

I am thinking about it from a monitoring perspective.

So lets say I have some stored procedure code that exists in production and the stored procedure fails to execute from a syntax error. On the front end the user sees Java Runtime Exception blah...blah...

Currently the way we find out that this occurs is by the user reporting the error or someone on the application side reviewing Java logs.

I am trying to run a trace against my server to see if any sp's are generating exceptions. The sample code provided above was to give an example of something that can / and does occur when an app is not tested for every business rule and process.

Another example would be insert into some table a value but that value fails ddl integrity.

If my trace captured the stored procedure that had the exception error I could then evaluate if their was a logic problem, syntax problem, or application workflow issue, and proactivly address this area of the code.

Please keep in mind I am trying to support a legacy application for which I have not written any of the code.

I do appreciate your help.

You can do anything at www.zombo.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 18:07:49

In addition to INSERT Log thingy, add

RAISERROR ('An error occured in SPROC bla bla', 16, 1)

and handle the error (now a warning) niceley in code, without getting severe errors halting the application!

Peter Larsson
Helsingborg, Sweden

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-03-14 : 18:16:09

Thank you for the suggestion,

Darn I was really trying to avoid modifying the thousands of sp's I have running accross all my servers.

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources