What the SQL is happening withy my syntax? 1

[SQLSquirrel]

This is bizarre!...

My colleague ran this query in query analyzer against Database AAA on Server SQL14:

select top 1 staff_number from dbo.staff where user_id = 'Omb'

He gets back:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

I thought this was stupid so I independently coded (not copied) his query in query analyzer on my pc (not his) and
ran it against my copy of database AAA on a different Server SQL12 and I get the same result - doh!

BUT if I switch the same Query analyser session to USE database BBB on SQL012 - BBB was restored from a backup of AAA last night - I get back:

staff_number
------------
54616

(1 row(s) affected)

What is going on?... any offers?

 

[vongrunt]

I have that deja-vu feeling...

SQL Server 6.5? Or DB compatibility level below 70?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLSquirrel]

Both servers are SQL 7.00.1094 (sp4)

cheers

 

[vongrunt]

Then I guess one of databases was set to work in 6.0 or 6.5 mode. Can you check that?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLSquirrel]

Stunning!

You prompted me to remember that the housekeeping tasks for two of these three databases (the ones which fail) include steps to switch compatability levels back and forth between 6.5 and 7.0 (a feature required by our third party application to support code inherited from a previous versions of the app).

I switched the compatability to and fro - and presto! problem solved - many thanks.

 

[vongrunt]

Actually there was almost identical problem in this forum yesterday (thread183-1094978) - JamesLean nailed it down in a second.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)