Sql Errors not caught on My .net code

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

Transact-SQL (2008)

Sql Errors not caught on My .net code

Author

Topic

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2012-01-12 : 07:28:59

Hi,

I have a SP ,which calls another SP
if my child SP returns an custom error msg, will that get caught as exception in my .net code??

Actually for me, it is not catching...
Pls help me

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-12 : 07:39:09

That depends on at least two things:

1. Is the SP throwing an exception, or is it returning an error code? If it is returning an exception, you should be able to catch it in C# code. Would be System.Data.SqlClient.SqlException class.

2. Is the exception being caught by the outer SP? If so obviously it wouldn't bubble up to the C# code, unless of course, it is rethrowing the exception.

If the error code is returned (which seems to be the case from your description), you should look for the return code from the stored proc (assuming the outer SP is returning that error code). Would be SqlParameter.ParameterDirection = ReturnValue. http://msdn.microsoft.com/en-us/library/system.data.parameterdirection.aspx

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2012-01-12 : 07:50:24

i am using as follows to show error in my child SP
RAISERROR(N'No Record Found in T5606F Table for Component [%s]',16,0,@ComponentCd)

Will this be caught as an exception in my .net code?
actually cuttently it is not catching

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-12 : 08:05:45

It should be caught in the .Net code. Take a look at the last example on this page: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.aspx

If you to run that code, and instead of calling the NonExistantStoredProcedure call PROC1 (given below) yoou should be able to see the exception being caught in the .Net code.

CREATE PROC proc1
AS
	EXEC proc2
GO

CREATE PROC proc2
as
	RAISERROR(N'This is a message',16,1);
GO

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2012-01-12 : 08:22:52

In RAISERROR mathod , i have given '0' instead of '1' in severity level.

That is y , my exception not caught??

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-12 : 08:42:03

No, that should not be the reason. That is just a state variable for your own purposes and you can use anything between 0 and 255. http://msdn.microsoft.com/en-us/library/ms178592.aspx

To debug,I would try to run that test code that I posted earlier and see if you are able to catch the exception.

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2012-01-12 : 23:20:47

ok.. will try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources