Wrong Line Number reported in SQL Message Window

Posted on 2012-09-03
Last Modified: 2012-09-03
I am getting the following message when I run a procedure in SQL
Msg 8626, Level 16, State 1, Line 10

My script has over a 1000 lines of SQL in it and the message in not related to line 10 of the script.  Any suggestions on how to get the actual line number for the message?
Question by:bkjenner
    LVL 75

    Expert Comment

    by:Anthony Perkins
    The line number is always relative and closest to the start of the query with the error.

    Try posting the relevant part of the code, the error message and we may be able to help you more.

    Author Comment

    I wasnt so much looking help with the error message but more wondering how when you have a long sql script with multiple GO statements in it, how can you easily determine the line number for error messages.
    LVL 75

    Accepted Solution

    As I stated before the Line Number is relative, so you may think this is wrong. but if you have something like this:
    SELECT * FROM ValidTable
    SELECT  1/0

    What do you think the line number for the error "Divide by zero error encountered" should be?  If you thought it should be 3, you would be wrong.  It is 0.

    Here is what I suggest you do as a technique for debugging.  Next time you get an error, ignore the line number, especially if you are using GO command, but instead double click on the error.  It will take you to the line that generated the specific error.

    Author Comment

    Thanks for your help! Works great
    LVL 75

    Expert Comment

    by:Anthony Perkins
    And this:
    It is 0.
    Should of course have read:
    It is 1.

    Thanks for the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now