SSMS 2008 R2 Outlining At Incorrect Place

Question

I am using SSMS 2008R2 to connect to a SQL 2012 Enterprise database (if that matters). Based on the screenshot below you can see that the outlining that SSMS is adding is in the incorrect place. Any ideas on how to ensure that the outlining works correctly? Also, when an error occurs in the query the line numbers in the error in no way align to the line numbers in the query/file (off by hundreds).

I have blacked out some of the fields but I assure you the query runs fine so the syntax of the select statement is not the issue.

Answer 1

A quick answer is you can DOUBLE CLICK on the ERROR MESSAGE in SSMS and it will take you to the actual error, regardless if the Line # it's stating the error is at matches what you see inside SSMS... usually.

With that being said, here are some things to keep in mind...

BATCH

First, each BATCH will count against your line number. Run the code below. It will error out at line 34, though the error is at 39. The reason is we are taking up 5 lines with the first two batches QUOTED_IDENTIFIER and ANSI_NULLS. If you comment these out, the correct line number will be returned.

SELECT WITH WHERE / JOINS

If your error is somewhere in your WHERE and potentially a JOIN, the line number for the beginning of the statement will be returned. Comment out the first two batches, uncomment the SELECT at the bottom where we divide by 0, and comment out the SET @testing = 'one' at the bottom. You'll notice Line 34 is what will be returned in the Error, which is the start of the statement, but the actual error is on line 37.

ERROR IN SELECT

Apparently the type of error, which I haven't narrowed down--but not Level and State-- affects the line number. I can't tell you why but it's something to keep in mind. In the divide by 0 error, if you double click on the error message it will take you to the SELECT statement versus the actual line number of the error. This is the "usually" clause i mentioned at the top of the post. Here is a code snippet.

SELECT
    1/1,
    1/2,
    --a.b --Errors at the actual line
    1/0   --Errors at the SELECT line number
WHERE
    1=1

ERROR_LINE()

You can use ERROR_LINE() to return the error line, but this is going to mimic what you see in the error in SSMS. So if you have multiple batches it will be "incorrect".

WORK AROUND?

Perhaps math... each SELECT as you suggested, depending on the error, can be counted as 1 line. So having a counter of lines in your code (BATCH + 1 for each select) could help you narrow down the error potentially. Or, if it's a procedure, look into sp_helptext

---

CODE FOR TESTING

SET QUOTED_IDENTIFIER OFF 
GO

SET ANSI_NULLS ON
GO

SET NOCOUNT ON

SELECT
    CASE
        WHEN 1=1 THEN 1
        ELSE 2
END



DECLARE @Testing int

SET @Testing = 1

IF @Testing = 1

    BEGIN
        SELECT
            'Testing is set to 1'
    END
ELSE
    BEGIN
        SELECT
            'Testing is not set to 1'
    END


--SELECT
    --1
-- WHERE
    --1 = @Testing / 0

SET @Testing = 'one'

GO