How Does SQL count lines?

Question

How Does SQL count lines?

splat33756

Ten Centuries

Points: 1177
More actions
June 6, 2003 at 7:11 am

#82632

When you get errors in Q/A or you are using traces to track Deadlocks. SQL will tell you which line the error was on. But how does SQL count lines? Does it ignore blank lines, and comment lines? Does it count lines as per statement?
For example if I format my create table statement all pretty on 10 seperate lines would that be 10 or 1 lines?
I've run several tests to figure this out and still can't. Any help is apprecated

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi splat33756,

quote:
When you get errors in Q/A or you are using traces to track Deadlocks. SQL will tell you which line the error was on. But how does SQL count lines? Does it ignore blank lines, and comment lines? Does it count lines as per statement?
For example if I format my create table statement all pretty on 10 seperate lines would that be 10 or 1 lines?

it seems that QA starts counting with 1 when you have no blank line at the beginning. When you start with a blank line, QA seems to start counting at 0.

But that's just a wild guess.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Ok to try and explain what happens.

First the query engine makes two passes across a query. The first time it crosses to validate syntax, the second is data.

Second QA use GO as a seperator and line is relative to GO or if you highlight a query section to run it is relative to your highlighted area.

Third whitespace after a GO but before the query or before the query in a highlighted area (also keep go in mind here) will be considered part of the query as line 1 of the data validation but not on the syntax check.

Forth if the error states syntax near 'blah' the line number will reflect the 'blah' line number and do to ( not bing closed can be several lines away in reality.

Fifth table names throw syntax errors but columns throw data validation errors.

Ok so let's test a few, let's create a table to test with first.



CREATE TABLE Test1 (

idx INT INDENTITY(1,1) NOT NULL PRIMARY KEY,

ny VARCHAR(3) NOT NULL,

nx VARCHAR(6) NULL

)

Now we don't need to add data so let's do the following



SELECT

*

FRO Test1

and run. You will get Line 3 as a syntax issue. Click the error in the report section of QA and it will put you at the line

FRO Test1

Now run



SELECT

ny,

nx

FROM

Test1

WHERE

n = 'bob'

and run. You will get the error Invalid Column and the Line will be 1.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Antares686,

quote:
SELECT
*
FRO Test1
and run. You will get Line 3 as a syntax issue. Click the error in the report section of QA and it will put you at the line
FRO Test1
Now run

SELECT
ny,
nx
FROM
Test1
WHERE
n = 'bob'
and run. You will get the error Invalid Column and the Line will be 1.

woooh, I didn't know that QA is such a highly sophisticated tool

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

splat33756 Ten Centuries Points: 1177 More actions · Answer 4

I get the first example with the syntax error. The second one with the data validation I'm still confused on. You said:

quote:
Third whitespace after a GO but before the query or before the query in a highlighted area (also keep go in mind here) will be considered part of the query as line 1 of the data validation but not on the syntax check.
-- snip --

SELECT
ny,
nx
FROM
Test1
WHERE
n = 'bob'
and run. You will get the error Invalid Column and the Line will be 1.

I'm still confused by this one. Is it because this is all one statement?

I tried:



SELECT

*

FROM

LINETEST

WHERE

R = 5

and got the same result "erorr on line 1"

PBirch SSCrazy Eights Points: 8483 More actions · Answer 5

Ok, just a guess, but here goes.

If the query fails because of syntax error QA will let you know about what line.

For example,

DECLARE @hat varchar(10)

@coat varchar(20)

will return an error on line 2 (even though the error could be line 1 - depends on where you put your commas).

But if the query passes the syntax but has invalid columns, joins, etc then it will error on the SELECT since the query itself is bad.

That's how it seems to me.

Patrick

Quand on parle du loup, on en voit la queue

Antares686 SSC Guru Points: 125444 More actions · Answer 6

pibirch if you had a comma and spelled varchar wrong on the secound line it would show line 2.

In the

---I think you mean there is whitespace here--

SELECT

*

FROM

LINETEST

WHERE

R = 5

If the data validation was invalid (not the syntax) and you ran without highlighting in most all test cases (but there is some gray areas on whitespace relation) if you clicked on the error message in the report section your cursor would jump to the whitespace area as opposed to the word select. But yes the error would read Line 1.

PBirch SSCrazy Eights Points: 8483 More actions · Answer 7

Right about the comma part, and certainly if I misspelled varchar.

Thanks,

Patrick

Quand on parle du loup, on en voit la queue