Error_Number does not return right value

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)

Error_Number does not return right value

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2007-08-06 : 16:12:42

Hi,
I am using the TRY/CATCH BLOCK TO TRAP THE ERRORS IN MY SQL SCRIPT.
Within my Try block,I am calling number of stored procedures and the error occurs at line number 15.
But my catch block reports the error occured in line 0.
I am confused and googled it for a while.It should return the line number where the error occured in try block.But its not returning the right line number.
Your help would be much appreciated.

My statement looks like this.
begin try
exec sp1
exec sp2
exec 2p3
exec sp4
---etc error occurs at line number 15
end try
BEGIN CATCH
PRINT 'Error Detected'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

END CATCH

Thanks,
Raju

Thanks!
Raju
http://rajusqlblog.blogspot.com/

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-08-07 : 10:26:47

Please note that the line number that is captured is the line number from WITHIN the stored procedure, and not the line where you called it.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-07 : 10:27:22

try just exec BombingSPName alone inside the try block and see what error_line you get

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources