August 14, 2018 at 2:22 pm
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 14, 2018 at 2:44 pm
Does putting square brackets around external solve the problem?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 14, 2018 at 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.
August 14, 2018 at 3:12 pm
yoginraval - Tuesday, August 14, 2018 2:55 PMYes, 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 15, 2018 at 6:23 am
yoginraval - Tuesday, August 14, 2018 2:55 PMYes, 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 15, 2018 at 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
August 15, 2018 at 10:07 am
yoginraval - Wednesday, August 15, 2018 10:01 AMThere 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.
August 15, 2018 at 12:45 pm
sgmunson - Wednesday, August 15, 2018 6:23 AMyoginraval - Tuesday, August 14, 2018 2:55 PMYes, 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
August 15, 2018 at 1:00 pm
Sue_H - Wednesday, August 15, 2018 12:45 PMsgmunson - Wednesday, August 15, 2018 6:23 AMyoginraval - Tuesday, August 14, 2018 2:55 PMYes, 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.
August 15, 2018 at 1:01 pm
Sue_H - Wednesday, August 15, 2018 12:45 PMsgmunson - Wednesday, August 15, 2018 6:23 AMyoginraval - Tuesday, August 14, 2018 2:55 PMYes, 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 15, 2018 at 1:11 pm
Sue
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