SQL Server debugging: break on error?

Question

I can't find any debug options when debugging an SP in SSMS on SQL 2008. Is it possible to have it break at the point when an error occurs, so I can examine where exactly the error is thrown and what the values of variables are at that point?

The error I'm trying to debug is:

Msg 8152, Level 16, State 14, Procedure xxx, Line 58 String or binary data would be truncated.

Answer 1

If you set a breakpoint early on you can step through until you find the error.

As a heads-up though, you need to be looking for a case where you're putting a string into a varchar that's longer than the varchar allows.

So, if you have a varcahr(5) and you try and set it to be 'mylongtext' you'll get that error.

---

To do this in SSMS, right click on the SP and choose EXECUTE TO > New Window. This will create the execute code for you:

DECLARE @RC int
DECLARE @LocationID int
DECLARE @SiteID int

-- TODO: Set parameter values here.

EXECUTE @RC = [MY-DB].[dbo].[P_SELECT_RetrieveToolTips] 
   @LocationID
  ,@SiteID
GO

Move cursor to the EXECUTE statement and press F9 to add a breakpoint.

Then press play (debug). When it stops on the breakpoint, press F11 to step into the SProc and then F10 to step along each line.

Answer 2

You can use Sql Query Analyzer for that. Your message means you are trying to save some value in a field, which length is smaller than the length of that value.