How to capture error code and error message? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

How to capture error code and error message?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-26 : 11:13:03

Sandy writes "Running a data load script on SQL*SERVER.

When an insert statement fails for any reason, need
to identify why it didn't insert. Need the error code
and error message resulting from the insert. How do I get it?

if @@error <> 0
PRINT @@ERROR

@@ERROR value always prints zero, doesn't make sense to me.

thanks in advance
Sandy"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-26 : 12:17:55

@@ERROR is being reset by "if @@ERROR<>0" which succeeds. Always use a local variable to trap @@ERROR and @@ROWCOUNT to avoid this problem.


declare @err int ; set @err = 0

insert table .......
select @err=@@ERROR

If @err<>0
begin
      error handling code/raiserror
end

BOL has some very wrong examples of using @@ERROR and @@ROWCOUNT that don't help matters
but these should hopefully be updated soon .However SQL will raise errors itself based on what type of problem occurs i.e. CONSTRAINT VIOLATIONS. If you trap @@ERROR and select the message text from sysmessages you will get the errror message with the placeholders intact and not the object names. What errors are you trying to trap. Can you create your own meaningfull error messages if SQL is not raising errors itself.Are you running a SQL script via QA or osql ?

HTH
Jasper Smith

Edited by - jasper_smith on 08/26/2002 12:19:45

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources