0

New to the SQL Server Express 2008 debugger.

Here's what happens. Let's say I have stored proc with 4 delete statements and the 2nd delete results in a FK violation on some other table. When I exec the sp and flip to the messages tab (which is next to the results tab) I can see the fk violation error in red color.

However if I step into the sp and go line by line there is no live error display. Basically there is no messages tab at all when you step into a sp. I see the call stack window, watch window, breakpoints window etc. I don't have any try catch or the classic if (@@errror) goto there type lines and putting error handling will probably be detected correctly in debugger as well.

I plan to do error handling but for now my question pertains to the use of the debugger. As soon as I stop the debugger the messages tab appears and I can see the fk violations. How do I see my errors live instead of having to wait to stop the debugger ? thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gullu
  • 3,477
  • 7
  • 43
  • 70

1 Answers1

1

I would put in the try catch statements and log the error messages to another table with an auto incrimenting sequence number. Then you can select from that to see which error messages and what order they came.

Example:

CREATE TABLE trackedErrors (
      sequence int not null identity(1,1) primary key
    , message varchar(max)
)
GO

BEGIN TRY
    --dangerous sql
    SELECT 5/0
END TRY
BEGIN CATCH
    --log error
    INSERT INTO trackedErrors(message)
    VALUES (ERROR_MESSAGE())
END CATCH

You can do a lot more with error information besides ERROR_MESSAGE(). See the TRY-CATCH documentation.

Aaron Silverman
  • 22,070
  • 21
  • 83
  • 103
  • In visual studio while debugging if an exception is not handled it catches it and shows you the error. I am looking for some solution like this. (maybe some hack or registry fix etc). Basically while debugging there is no indication that there was an error unless you handle it as you have shown. thanks – Gullu Jul 18 '11 at 15:51
  • The stored procedure will keep executing even once it hits an error, visual studio will only report on the most recent error. By using my solution you can in, SQL Server Management Studio, select from the table to see the errors live. If you insist on seeing the erros in the debugger, you can add some extra logic to the solution that selects the newly added errors at the very end of the stored procedure, build an error message, and call RAISERROR. – Aaron Silverman Jul 18 '11 at 15:59
  • I like your solution in the absence of any hack or workaround. Hence up voting your answer. Even if I can see the most recent error like VS I will be happy. Anyone ? thanks – Gullu Jul 18 '11 at 16:23