Extension overrides Postgres syntax

[BenWildeman]

• MSSQL Extension Version: 0.3.0
• VSCode Version: 1.10.2
• OS Version: Windows 10

Steps to Reproduce:

1. Install MSSQL
2. Write PG SQL within a .sql file
3. Cry because of all the "errors"

When having to write SQL for both MSSQL and Postgres for my job, it's rather annoying that this extension has no idea about other SQL syntax that's very much valid

[kevcunnane]

@NiTrOGhost thanks for raising this. We're somewhat limited by how VSCode supports language extensions - for a given file type, 1 editor is chosen. What might work is if you modified the PostGre extension to have additional, more specific file type (e.g. .pg.sql) and that way it would get used instead of our extension when opening that file type.

Regarding support for PostGre - this isn't in the plans for right now. With community involvement we could certainly look at not listing errors on syntax / allowing a setting to disable our parsing on files. Again there are some limitations since VSCode accepts a syntax file for a lot of its processing, but it's certainly interesting to think about.

[kburtram]

@NiTrOGhost another possible workaround for this issue is to add "mssql.intelliSense.enableErrorChecking": false option to your settings file. This will turn off error highlighting for SQL files, but you'll still be able to use other language service and query execution features.

Longer term we can look at possibly detecting Postgres or MySQL syntax and disabling our error checker for those SQL files.

[aiingstan]

@kburtram how's the progress now? I'm including some MySQL .sql scripts in my project, jump into this annoying issue. How about adding a configuration item to exclude specified file extension, or read the very first line of the .sql file to determine syntax? Like shebang #! in Linux world.

[jcush]

@kburtram @kevcunnane
Is there any hope of this arriving soon? I have to work with both T-SQL and MySQL daily and after 2-3 weeks had over 2k "problems" in VSCode

I can confirm that Karl's suggestion of setting mssql.intelliSense.enableErrorChecking to false in the settings file does suppress all errors, but I do mean all errors (T-SQL included). It's not ideal, but if you know how to wrap queries in transactions then the only negative effect of this solution is inconvenience

[Zireael]

vscode-mssql fails to recognise Postgres syntax and incorrectly reports errors. It's very annoying and for this reason I've had to disable the extension.
Please expand to recognise Postgres syntax, or find a way to stop reporting it as errors.

[siben168]

can we put this into queue now as this is very annoying that vscode cannot recognize the syntax for postgresql.

[kobenauf]

Setting mssql.intelliSense.enableErrorChecking to false isn't a great solution. It does quit falsely calling out valid pg syntax (as well as actual problems), but formatting gets applied up until an "error" occurs and is not applied past that point. For example, converting types to upper case works up until pg-only syntax is encountered, and then no more formatting is applied.

[firxworx]

Ack... I had vscode applying the red error underline to postgres sql. I saw no other option other than "sql" in "Change Language Mode". I hit Google and found this issue... 2017!!!!

This issue still exists on the latest VSCode + mssql extension at the time of writing w/ WSL2 on a Windows 11 machine.

I disabled the mssql extension and all is good now: my postgres-flavour sql files highlight as they should. I only need mssql support occasionally so thankfully its not much hassle in my case to disable the extension.

Re triage -- I'm sure cases like mine are exactly what Microsoft would prefer to avoid: a dev who codes on both Mac + PC workstations and who might be tempted/swayed to propose SQL Server for a work or client project where it might be a good fit... but not if the tooling is going to annoy the heck out of me!

Auto detection and not overriding in the postgres sql case would be great. This issue exists even for files that have a psql extension! I would have probably kept the extension with less, e.g. if there was a way to disable its behaviour for a workspace/folder/file without disabling everything wholesale.

[kburtram]

@lewis-sanchez I think the suggestion to detect some common non-ANSI/non-T-SQL syntax and turning off the language service makes sense here. Could you please take a look?

[kburtram]

@lewis-sanchez This seems somewhat related #17298, at least if I understand correctly, the underlying problem is the same (MSSQL syntax is being applied when it's not wanted). Maybe we can address that at the same time as this?

[lewis-sanchez]

@kburtram, a few ways to solve this issue. Please provided your thoughts on which one should be implemented.

Approach 1 - Add a new setting to turn off language services by default

This approach goes in line with issue #17298

A new setting would be added to turn off language services by defaulting the selected language in the status bar to "None" on every editor.

Language services could be turned back on by clicking on "None" and choosing "MSSQL" as the SQL language:

The language service activation process would only turn on the language service for the current editor, to prevent IntelliSense from being applied to all SQL documents.

Approach 2 - Turn off Language Service when PostgreSQL or MySql Syntax is Detected

Turn off the language service when reserved words and keywords for PostgreSQL and MySQL are used in an editor.

Approach 3 - Automatically turn off IntelliSense when an error limit is reached.

This approach could turn off IntelliSense error checking when an error limit is reached. The limit could initially be set to something like 10, but could also be custom set by the user. When the error limit is met, the MSSQL language service would be turned off automatically.

[Benjin]

#1152 has another idea for solution:

Although it is possible to disable the extension for a workspace, this is less helpful in mixed environments -- for example I am currently working on web services that talk to both MSSQL and SqlAnywhere databases at the same time. It would be highly desirable to be able to suppress processing of non-Microsoft SQL with some kind of annotation at the start of a file, ideally in a comment like

--IGNORESQL

Even better would be a setting

Process SQL files: { When marked with --MSSQL | Except when marked with --IGNORESQL }

which would allow the user choice between opt-in and opt-out as suits the workspace.