A friend of me is using a SQL Server database for some programming in his leisure time. When he wants to execute any query which contain a NOT IN clause in SQL Server Management Studio, this error appears:

Msg 102 Level 15 State 1 Line 1
Incorrect syntax near ';'

It's well known that this kind of error occurs when you provide an incorrect query, but the query is correct. The issue only appears when the query contains a NOT IN clause. Please have a look on this fiddle example: db<>fiddle

In fiddle and also in my own DB, all these queries will successfully be executed, but in SSMS of my friend, the last query fails (all others succeed!) and the error message named above comes up. It seems like SSMS removes the last bracket or does something similar strange. I know the sample queries don't make much sense, it's just to illustrate the problem.

We tried the following to find out what's happening:

1. Created a backup of his database and imported it on my system -> the query is successfully executed
2. Checked if any triggers/indexes or similar exist on the affected column or table -> this is not the case
3. Pressed Ctrl L to execute the execution plan for the query -> the same error message appears
4. Executed the query in his C# application for which he uses the database -> the query is successfully executed
5. Removed and reinstalled SSMS on his system and imported the database backup again -> the behavior is still the same

I've been working for many years with SQL and also SQL Server Management Studio, but I've never seen such a strange behavior and we couldn't find any hints on this in the net.

We both made sure we are using the latest version of SSMS and we can't see any differences between his and mine setup that would explain this issue. It seems like there is any hidden setting on his SSMS that generally prevents executing such queries and even remains after removing and reinstalling it. Any help will be very welcome.

CodePudding user response：

Thanks very much to all who had a look and tried to find the reason of this issue. Since even with these quite good ideas the problem couldn't be resolved, we uninstalled SSMS again and then used CCleaner to clean the registry and possible relicts of uninstalled programs. Then we reinstalled SQL Server Management Studio, imported the DB backup again and now it's working correctly. Sorry I can't tell you what exactly was the issue, it seems that somehow an entry concerning SSMS in the registry or any configuration file was incorrect.