0

Context: I have a Python script I'm scheduling to execute a proc in SQL Server which in turn uses DB mail to send an email with HTML body content. See Python and T-SQL. I'm getting the error below.

Error

Traceback (most recent call last):
File "C:\Users\eb\Desktop\CATTOOL_Send_Email_Function.py", line 24, in 
<module>
connection_cursor.execute(executesp)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL 
Server][SQL Server]Incorrect syntax near '.'. (102) (SQLExecDirectW)")
>>>

Python script

username = '***'
password = '***'

connection = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};"
                "Server=MSS-ProdSQL-01;"
                "Database=ORTeam;"
                "Username="+username+";"
                "Password="+password+";"
                "Trusted_Connection=yes;")
connection_cursor = connection.cursor()

executesp = f"USE ORTeam EXECUTE AS LOGIN = 'sa'; EXEC cp.Send_Chargecode_Changes 
@emailto = {emailto}, @emailhtml = {emailhtml}"

connection.autocommit = True
connection_cursor.execute(executesp)
connection.close()

SQL Server Proc

ALTER PROCEDURE [cp].[Send_Chargecode_Changes] 
            @emailto NVARCHAR(MAX),  
            @emailhtml NVARCHAR(MAX) 

AS 

EXECUTE AS LOGIN = 'sa'
EXEC msdb.dbo.sp_send_dbmail  
@execute_query_database = 'ORTeam',
@profile_name = 'Maintenance Task Notifications',  
@recipients = @emailto ,
@body_format = 'HTML',
@body = @emailhtml

Question: I'm sure this is some kind of string related error in passing through the parameters but I'm not sure how to resolve, please help.

emalcolmb
  • 1,585
  • 4
  • 18
  • 43
  • You need to parameterize this properly, rather than injecting the data into the SQL batch. `EXEC cp.Send_Chargecode_Changes @emailto = ?, @emailhtml = ?`. Side note: a user which is allowed to run `EXECUTE AS LOGIN = 'sa'` is presumably a `sysadmin` already, so it doesn't make much sense to switch the current login. Perhaps you actually wanted to declare the *procedure itself* as `WITH EXECUTE AS 'sa'`, see https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver16 – Charlieface Oct 31 '22 at 22:29
  • Another thing to think about: SQL Server is not a generalized mailing app, you should use Python client libraries or a proper mail server to do that. – Charlieface Oct 31 '22 at 22:30

0 Answers0