The SFMC SQL Comment Trap That Breaks Query Activities
I was really stoked when I found out that the SQL Query Editor in Salesforce Marketing Cloud has IDE shortcuts, including one for adding comments. Basically, you select any piece of SQL code and press Ctrl + /, which comments out the selected lines. Marketing cloud automatically add to every line --, which is valid single line comment used in SQL.
I was so happy that this polished feature from code editors like VS Code found its way into Marketing Cloud. The script validated successfully – green checkmark and all. But when I tried to save the query, I got a weird error. The last thing I would have suspected was that the issue was caused by a feature provided by Salesforce itself. It turned out to be a bit of a honey trap that made me debug my query for quite some time before I realized what was happening.
Those comments are technically valid SQL comments. The validator accepts them, but saving the query throws an error. The funny thing is that a simple fix from Salesforce would be to wrap the comments in /* */ instead but nope it would be very simple and we are bored at work so we are very happy to experience new surprise every now and then.
The lesson learnt here is to not use single line comments and do not use the IDE command to auto comment selected lines.
