Schemachange is failing when we have any comments in the code

[praneeth1987]

I have below sql file which needs to be deployed

create table --------;
//alter table

After testing in DEV, I no longer need alter table command.
I have commented out alter table command while promoting to TEST & PROD , but it is failing with below error

snowflake.connector.errors.ProgrammingError: 000900 (42601): SQL compilation error:
Empty SQL statement.

As per this https://community.snowflake.com/s/article/Empty-SQL-Statement-When-Through-Python-Code-When-We-Have-Commented-Lines-In-the-Code-File
We need to use the set remove_comments=True,
Do you have any alternative for this or is this known issue?

[sfc-gh-jhansen]

Hey there @praneeth1987, thanks for bringing this up. I'm not sure what the right fix is here. If we add remove_comments=True to the execute_string() call then all comments in the script will be removed. Some customers rely on comments in their queries for various reasons.

The problem appears to be having a comment on the last line of the script: https://community.snowflake.com/s/article/Comments-causes-SQL-compilation-error-Empty-SQL-statement.

I would suggest just removing that from the script. Are you using source control? In general it's a best practice to not check in/commit code with comments and source control would help you keep the version history in case you needed to revert at some point. Just a thought.

[sikfink]

This caught me for a while too as I was using a comment at the end of my scripts:
--End
to signify the end of the script. I removed the final comment and the tool works as expected running from inside Azure DevOps.

[sfc-gh-tmathew]

Hello @praneeth1987

As suggested above the workaround is to avoid comments in the last line of the SQL file. We do not plan to fix this anytime soon.

We cannot strip comments for reasons stated earlier. Therefore, closing the issue for now.

[pimlottc]

The problem appears to be having a comment on the last line of the script: https://community.snowflake.com/s/article/Comments-causes-SQL-compilation-error-Empty-SQL-statement.

This link is now a 404, is there an updated link to explain why SQL statements cannot end with a comment?

[sfc-gh-tmathew]

@pimlottc here is the updated link https://community.snowflake.com/s/article/Empty-SQL-Statement-Through-Python-Code-When-We-Have-Commented-Lines-In-the-Code-File