[Python] Python pyodbc SQL script execution fails with "Missing end comment mark '*/'" error...

I'm using Python 3.x with pyodbc to execute SQL scripts against a SQL Server database. The scripts are stored in .sql files and are executed in batches from a folder. When running the script, I get this error from SQL Server

Error while executing TABLE CREATION (0000.dbo.MESSAGE_MST.Table.sql): ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Missing end comment mark '*/'. (113) (SQLExecDirectW)")

It seems related to unbalanced block comments (/* ... */) in the SQL files, but manually checking them doesn't always reveal obvious mismatches. The word "standards" appears in some comments, and I suspect there might be encoding or character issues causing this. Here's the relevant part of my Python code from a common functions module that handles reading and executing the SQL scripts. It uses chardet to detect encoding automatically

import chardet
import pyodbc
import logging

def execute_sql_script(server: str, database: str, script_path: str, operation_name: str) -> dict:

logging.info(f"Executing script: {os.path.basename(script_path)} for '{operation_name}'")

try:
# Read the file in binary mode to get the raw bytes.
with open(script_path, 'rb') as f:
raw_data = f.read()

# Use chardet to detect the encoding.
detection_result = chardet.detect(raw_data)
file_encoding = detection_result['encoding']
logging.info(f"Detected encoding for {os.path.basename(script_path)}: {file_encoding}")

cnxn_str = (
f"DRIVER={{ODBC Driver 17 for SQL Server}};"
f"SERVER={server};"
f"DATABASE={database};"
"Trusted_Connection=yes;"
"Encrypt=yes;"
"TrustServerCertificate=yes;"
)
with pyodbc.connect(cnxn_str) as cnxn:
with cnxn.cursor() as cursor:
# Decode with detected encoding.
script_content = raw_data.decode(file_encoding)

# Execute script statements separated by 'GO'
for statement in script_content.split('GO'):
if statement.strip():
cursor.execute(statement)
cnxn.commit()

logging.info(f"Successfully executed script: {os.path.basename(script_path)}")
return {"Success": True}

except Exception as e:
error_message = f"Error while executing {operation_name} ({os.path.basename(script_path)}): {e}"
logging.error(error_message)
print(error_message)
return {
"Success": False,
"ErrorMessage": error_message,
"EmailSubject": f"{database} : {operation_name} - FAILED"
}


What I have tried:

• Using chardet to detect encoding.
• Checked some Stack Overflow answers suggesting codecs for reading files with error handling (e.g., codecs.open(..., errors='replace')), but it didn't fix the SQL syntax error. I've tried the solutions from various Stack Overflow posts on similar pyodbc and encoding issues, but they haven't resolved this for me.
• Manually opening and re-saving .sql files in Notepad++ as UTF-8, which sometimes helps for one file but not universally.
• Adding a preprocessing step to replace common bad characters (like smart quotes to straight quotes, em-dashes to --), which works in some cases but feels hacky.
• Looking into Stack Overflow suggestions about issues with 'GO' statements, such as improving the splitting logic to handle 'GO' inside comments. I've tried adjusting the code to avoid splitting on 'GO' within block comments (e.g., by parsing comments first), but some scripts still execute fine even when 'GO' is mentioned in comments, so this doesn't explain all the failures consistently.

I want a universal fix in Python to handle these .sql files reliably, perhaps better encoding detection/handling with codecs or chardet, or a way to sanitize the content before execution. Is there a robust way to read and decode these files to avoid SQL comment imbalances caused by hidden chars? Or is this purely a SQL parsing issue? For reference, here's the code from the failing file MESSAGE_MST.sql (it's creating a table called MESSAGE_MST, but the error points to comment issues)

/*-------------------------------------------------------------------------------------------------------------------------------------------------------------
AUTHOR:
DATE:
PURPOSE:
MODIFIED BY: DEEPAK KUMAR KC
MODIFIED DATE: MARCH-03-2025
*/
/*
Changes:
SlNO Date Modified By Description

-------------------------------------------------------------------------------------------------------------------------------------------------------------*/
USE CONVERSION_MASTER
GO

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'DBO.MESSAGE_MST') AND TYPE IN (N'U'))
BEGIN
CREATE TABLE DBO.MESSAGE_MST
(
XWALK_MESSAGE_ID INT IDENTITY(1,1) NOT NULL,
CONV_SCRIPT NVARCHAR(500) NOT NULL,
TARGET_TABLE NVARCHAR(200) ,
STATUS NVARCHAR(200) NOT NULL,
STATUS_NO NVARCHAR(200) NOT NULL,
INFORMATION NVARCHAR(2000) NOT NULL,
ERR_PRIORITY INT NULL
)
PRINT 'TABLE CREATED MESSAGE_MST'

END
GO

/*
IF NOT EXISTS(SELECT 1 FROM SYS.INDEXES WHERE NAME = 'NCUI_MESSAGE_MST_CONV_SCRIPT_TRG_TABLE_ERROR_NO')
BEGIN
CREATE UNIQUE NONCLUSTERED INDEX NCUI_MESSAGE_MST_CONV_SCRIPT_TRG_TABLE_ERROR_NO ON MESSAGE_MST(CONV_SCRIPT, TARGET_TABLE, STATUS_NO)
END

GO
*/

/*
DROP TABLE MESSAGE_MST
TRUNCATE TABLE MESSAGE_MST
SELECT * FROM MESSAGE_MST

DROP INDEX NCUI_MESSAGE_MST_CONV_SCRIPT_TRG_TABLE_ERROR_NO ON MESSAGE_MST;
*/

Continue reading...