SQL SERVER 2005/2008 TRY CATCH & RAISERROR

  • i have tried to use try/catch/raiserror like in this example :

    http://msdn.microsoft.com/en-us/library/ms178592.aspx

    u used it beacuse i didnt want from my vb6 application to get errors on duplicate keys, but i wanted to get any other error.

    the Problem is that the RaisError dosen't have the effect that if the exception that the sql server throws on "duplickate key" error for example.

    if i dont use try/catch in the vb6 i will get the full error "Primary Key violation....."

    but when i rap it with try/catch and make another error, and use the RaiseError i don't get the error back from the SQL SERVER

    any idea why?

  • Using Try/Catch with raiserror should get you the error message you want into VB. It will get you a message number of over 50 000 which means it's a user defined message, but it will get you a message.

    Can you show us your code? Maybe there is a small glitch.

    Cheers,

    J-F

  • this is the vb6 code : (when i dont use the try/catch/error i do get back the error on line (***)

    g_Command.Execute -- executes the STORED PROCEDURE

    If Err.Number 0 Then

    MsgBox Err.Description & vbCrLf & vbCrLf & Err.Number '(***)

    End If

    this is the STORED PROCEDURE :

    DECLARE @Err int ,@msg_id bigint

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity INT;

    DECLARE @ErrorState INT;

    BEGIN TRY

    INSERT vw_Billing WITH(ROWLOCK)......

    VALUES .....

    END TRY

    BEGIN CATCH

    SET @Err = @@ERROR

    SELECT @ErrorMessage = ERROR_MESSAGE(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE();

    SET @msg_id = 50000+@Err

    END CATCH

    IF(@Err=0)

    BEGIN

    RETURN 1

    END

    ELSE

    BEGIN

    IF @Err = 2767 RETURN 0 --duplicate Key Error

    print @ErrorSeverity --=>16

    print @ErrorState--=>1

    RAISERROR(@msg_id,@ErrorSeverity,@ErrorState)

    --SET @@ERROR =

    RETURN 0

    END

  • Using an error message ID in RAISERROR requires adding the error message text to sys.messages.

    You can use custom message text in RAISERROR, but the error number will be 50000.

  • thats not the problem

    beacuse i added aleardy all the messages that i wanted to sys.messages

  • If you execute the procedure in SSMS, do you see the raiserror output in red? If not, maybe the message was not registered. You could try to send a custom message, and see how your VB code handles it.

    If you see the message in SSMS, then your VB code should probably include a try catch, even though I'm not really aware of how VB6 works, it's the way we work in VB.NET. Using a try catch in VB you should be able to trap the error message, and display it programmatically.

    If it's not any of those answers, maybe I don't understand your problem correctly.

    Cheers,

    J-F

  • i send data to a STORED PROCEDURE in the db.

    this STORED PROCEDURE insert data and raiserror.

    the thing is that the problem is between how an exception occusres when for example the is a Primary Key error (and this is just an example, theere copild be anything else) or how RAISERROR occures.

    it seem's it dosen't have the same effect by returning an error back to the vb (which that i do handle, all the point that when there is try/catch in sql i dont see any more the error in vb - while i do want to see them)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply