SQL Server 2016 syntax error

Question

SQL Server 2016 syntax error

yoginraval

Old Hand

Points: 394
More actions
August 14, 2018 at 2:22 pm

#371089

Hi All,
We are in process to upgrade our SQL Server database from SQL Server 2008 to SQL Server 2016. The below query works perfectly fine in sql server 2008 but fails in SQL Server 2016 with error "Incorrect syntax near the keyword 'EXTERNAL'"
select m.EXTERNAL from mytable
I do understand that EXTERNAL is a keyword both in sql 2008 and sql 2016. Is there any database level settings other than changing compatibility level that will allow to use the query in the same in sql 2016. We are trying to avoid any code changes, hence we do not want to modify the query unless absolutely necessary.
August 16, 2018 at 7:11 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Does putting square brackets around external solve the problem?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yoginraval Old Hand Points: 394 More actions · Answer 2

Yes, putting square brackets around external does solve the problem. But we are trying to avoid any code changes and looking for any other possible options.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

yoginraval - Tuesday, August 14, 2018 2:55 PM
Yes, putting square brackets around external does solve the problem. But we are trying to avoid any code changes and looking for any other possible options.

This is arguably not a code change as such, but I know what you mean.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 4

yoginraval - Tuesday, August 14, 2018 2:55 PM
Yes, putting square brackets around external does solve the problem. But we are trying to avoid any code changes and looking for any other possible options.

The query you posted would not actually run on either 2008 or 2016, as you don't have the table named mytable aliased as m. With the alias in place, I would expect it to run in both... although I don't have a 2008 instance to test with.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

yoginraval Old Hand Points: 394 More actions · Answer 5

There was typing mistake in the query I posted in the forum, the actual query does has alias in place (see the updated query below). The query does not work in sql 2016 unless I put a square bracket around "EXTERNAL". I hoping to avoid the usage of square brackets around keyword external, if at all that is possible in sql 2016.

select m.EXTERNAL from mytable m

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

yoginraval - Wednesday, August 15, 2018 10:01 AM
There was typing mistake in the query I posted in the forum, the actual query does has alias in place (see the updated query below). The query does not work in sql 2016 unless I put a square bracket around "EXTERNAL". I hoping to avoid the usage of square brackets around keyword external, if at all that is possible in sql 2016.
select m.EXTERNAL from mytable m

Sorry, EXTERNAL is a reserved word. To use it otherwise requires that you put it between square brackets.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017

Sue_H SSC Guru Points: 90860 More actions · Answer 7

sgmunson - Wednesday, August 15, 2018 6:23 AM
yoginraval - Tuesday, August 14, 2018 2:55 PM
Yes, putting square brackets around external does solve the problem. But we are trying to avoid any code changes and looking for any other possible options.
The query you posted would not actually run on either 2008 or 2016, as you don't have the table named mytable aliased as m. With the alias in place, I would expect it to run in both... although I don't have a 2008 instance to test with.

Going as far back as I could, I just tested it with 2005 and get the error unless I use the brackets. So I'm missing something - how would it actually have worked on 2008 without the brackets?

Sue

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 8

Sue_H - Wednesday, August 15, 2018 12:45 PM
sgmunson - Wednesday, August 15, 2018 6:23 AM
yoginraval - Tuesday, August 14, 2018 2:55 PM
Yes, putting square brackets around external does solve the problem. But we are trying to avoid any code changes and looking for any other possible options.
The query you posted would not actually run on either 2008 or 2016, as you don't have the table named mytable aliased as m. With the alias in place, I would expect it to run in both... although I don't have a 2008 instance to test with.
Going as far back as I could, I just tested it with 2005 and get the error unless I use the brackets. So I'm missing something - how would it actually have worked on 2008 without the brackets?
Sue

I just tested this on 2000. This works on SQL 2000, I guess the database compatibility mode is set to 80 on 2008 Instance where it's working.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 9

Sue_H - Wednesday, August 15, 2018 12:45 PM
sgmunson - Wednesday, August 15, 2018 6:23 AM
yoginraval - Tuesday, August 14, 2018 2:55 PM
Yes, putting square brackets around external does solve the problem. But we are trying to avoid any code changes and looking for any other possible options.
The query you posted would not actually run on either 2008 or 2016, as you don't have the table named mytable aliased as m. With the alias in place, I would expect it to run in both... although I don't have a 2008 instance to test with.
Going as far back as I could, I just tested it with 2005 and get the error unless I use the brackets. So I'm missing something - how would it actually have worked on 2008 without the brackets?
Sue

Yep, I had to set the compatibility to 2000 on a 2008 instance to have it parse correctly. My guess is they are going from 2000/2005 to 2016, but had to make a stop at 2008 on the way since you can't go directly from 2005 to 2016.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sue_H SSC Guru Points: 90860 More actions · Answer 10

Thanks for the extra testing. The jump to 2008 and then to 2016 makes sense.
With a big leap in versions, I would kind of doubt that an upgrade with no code changes is possible. A lot of things change over 10+ years.

Sue