Catching Error desc from stored procedure exection

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 2005 Forums

Transact-SQL (2005)

Catching Error desc from stored procedure exection

Author

Topic

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-18 : 12:32:00

Hi,

Can somebody tell me, how to catch error description from stored procedure execution.

I have added following type of code.

DECLARE @ret int, retmess varchar(50)
exec @ret=@Proc_name

IF @ret <> 0
BEGIN
print 'Error # is ' + ltrim(str(@ret)) + '.'
select @retmsg = [description] from master.dbo.sysmessages where error = @ret and msglangid=1033; -- for english
print 'Error Message - ' + @retmsg + '.'
END

Out put is something like this

The statement has been terminated.
Error # is 547.
Error Message - The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls..

This doesn't give me correct error message.

can anyone have better way of doing it ot correcting this?

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-18 : 12:34:08

Why don't you use
Begin try
....
end try
begin catch
....
End catch

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-18 : 13:03:40

Thanks Sodeep.

But in that case, I will have to explicitly code for different error messages. Can't I convert above system message into readable / correct format?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 13:10:52

The correct error description will be sent back to your application on error without any coding. For instance, say we are attempting to insert a duplicate row and we've got a constraint preventing it. You'll receive the duplicate error message back to your application. It'll have all the details already.

You do not need to look at sysmessages.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-18 : 13:14:44

Tara you are correct.

But I also want to catch this & insert into log table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 13:16:16

Then receive it from your application and send back to the log table.

I don't think you'll be able to do anything with sysmessages.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-18 : 13:20:52

How can one application pass error message to a string, to be inserted to database table further. I mean, I dont know

Here, my calling application is informatica and I get this message in session logs.
There is no way to catch error messageis into a string/variable through this logs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 13:29:56

I don't have any information about Informatica. From .NET, it would be very easy as you catch the error message in a Try/Catch via exception.message I believe.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TsqlWorld
Starting Member

14 Posts

Posted - 2008-07-18 : 13:37:32

Thanks Tara,

Anybody knows how to catch error description into log table in SQL server implicitely?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources