troubleshooting Question

Bubble user-defined Sql RAISERROR(...) to c# exception

Avatar of SamCash
SamCashFlag for United States of America asked on
Microsoft SQL ServerMySQL ServerC#PostgreSQLServer Software
13 Comments1 Solution1653 ViewsLast Modified:
EE,

Sql 2008 R2.  Unable to Bubble user-defined Sql RAISERROR(...) to c# exception.  

Simplified example usage; a SELECT correctly returns no rows because of the WHERE clause.  IF @@ROWCOUNT = 0; RAISEERROR(...) pass the error/exception to some higher level where it can be handled.

Evidence or clues.  Sql generated errors bubble to c# but Sql user-defined errors do not bubble to c#.  Both are logged in the sql server logs.

Simply toggle line ~ 28 from commented to un-commented to exhibit the issue.
"--select @firstNum + @secondNum as result --Un-comment me and watch the error come and go at the c# level???"

Simplified webform and sproc exhibiting improper behavior below.

Kind Regards
Sam

Sql:

USE [SQLUTL]
GO
/****** Object:  StoredProcedure [dbo].[Test_Sp]    Script Date: 10/26/2016 16:08:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Test_2_Sp]
       @firstNum int,
       @secondNum int
AS
BEGIN
LINENO 18

EXEC sp_addmessage   
    @msgnum = 50001,   
    @severity = 16,  
    @msgtext =   N'This is a user defined error message test_2_sp',
    @lang = 'us_english';  
    
DECLARE @ContactID_str nvarchar(16);
	BEGIN TRY
	
       --select @firstNum + @secondNum as result --Un-comment me and watch the error come and go???
       
       RAISERROR (50001, -1,-1) WITH Log; --https://msdn.microsoft.com/en-us/library/ms178592.aspx
	END TRY
	BEGIN CATCH
		declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
		select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		
		raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
	
	END CATCH       
EXEC sp_dropmessage 50001, 'all'; 
       
END

c#;

namespace SQLUTL.Code.WebForms.Public
{
    public partial class EE_Conn_Exception : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string ConnectionStringName = "SQLUTLtest";
            //string con = @"Data...";
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionStringName].ToString());

            using (SqlCommand cmd = new SqlCommand("Test_2_Sp", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@firstnum", "1");
                cmd.Parameters.AddWithValue("@secondnum", "0");
                
                sqlcon.Open();

                try
                {
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();
                    adp.Fill(dt);
                    Response.Write("Hello");
                }
                catch(Exception ex)
                {
                    Response.Write(ex.Message);
                }
                finally
                {
                    Response.Write("</br>finally");
                }
            }

        }
    }
}
ASKER CERTIFIED SOLUTION
Nakul Vachhrajani
Senior Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros