Strange Error on multi-line statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Strange Error on multi-line statement

Author

Topic

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-27 : 13:22:13

If the following is run:

update @tblVariable
set columnA = 57
where columnB = 57
and columnC is null

I get an error of Incorrect syntax near the keyword 'where'.

When I put the statement on ONE line:

update @tblVariable set columnA = 57 where columnB = 57 and columnC is null

It works.

The SQL is for a SP that is about 1000 lines long. This does not seem right to me. Any ideas or has anyone seen anything like this before?

Jay99

468 Posts

Posted - 2002-03-27 : 13:50:30

There's gotta be more to the story...

xp_MSexorcist

Jay
<O>

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-27 : 13:54:34

Actually no. The code I posted is about lines 500 to 550. When I try to process it gives the error. When I comment it out it compiles. When I uncomment it and put it on one line it works. I have another statement almost exactly like it below it and it works on multi lines. I know it sounds crazy but it's true. I HAVE WITNESSES!!

In speaking with some other co-workers they have also seen this. They seem to think that it may be due to some strange control characters getting into the editor. Thoughts?

-- And no I have not been drinking...yet.

Edited by - JamesT on 03/27/2002 13:59:01

Jay99

468 Posts

Posted - 2002-03-27 : 14:00:43

Snip the proc down the the absolute bare minimum code required to cause the error and then post . . .

Jay
<O>

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-27 : 14:45:17

I just ran the following and it works fine:


declare @tablevariable table(columnA int, columnB int, columnC int)

insert into @tablevariable values (10, 57, null)
 
update @tableVariable 
set columnA = 57 
where columnB = 57 
and columnC is null

-Chad

Jay99

468 Posts

Posted - 2002-03-27 : 14:51:13

quote:

...They seem to think that it may be due to some strange control characters getting into the editor....

What editor are you using?

Jay
<O>

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-27 : 15:27:27

I am using QA to write and edit the SQL. I just did the following: removed all other code besides that SQL statement and the table variable declaration. Still got the error. Copied the code to another editor (TextPad in this case), pasted it, copied it, pasted it back to QA and it compiled. There must be some bug that puts some control characters into the editor. I know I sound like i've been smoking spiders but it happened. If I try and type out what I am doing like chadmat did it would work because the control characters (or gremlins) are not there.

Jay99

468 Posts

Posted - 2002-03-27 : 15:30:37

No really, I believe you . . .

Jay
<O>

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-27 : 16:52:02

I used QA as well.

-Chad

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-27 : 17:24:53

I've had it a couple of times where a statement doesn't compile but does if you add some characters or such.

I think the last one was and order by that caused the problem

...
order by fld desc, fld2

failed

...
order by fld, fld2

worked
as did

...
-- ignore this line
order by fld desc, fld2

I suspect it is because the syntax checker has the odd bug or two when checking across buffers. It's a lot rarer not than it was in the early versions - and fortunately it doesn't change the text in syscomments for you anymore.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-27 : 17:51:38

Ah, sweet vindication. I'm not insane after all. Well, perhaps a bit unbalanced but hey, diversity is the spice of life.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-28 : 06:17:11