July 30, 2009 at 8:03 am
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?
July 30, 2009 at 8:21 am
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
July 30, 2009 at 8:30 am
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
July 30, 2009 at 9:02 am
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.
July 30, 2009 at 9:26 am
thats not the problem
beacuse i added aleardy all the messages that i wanted to sys.messages
July 30, 2009 at 9:58 am
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
July 31, 2009 at 6:43 am
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