2

I am trying to pass a variable file location be emailed via the my SQL server. If i am using some other string it will work fine but this is the error I am getting.

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Attachment file C:\\Users\\ME\\Desktop\\Queries\\Fail w email\\PythonEmail\\ErrorList.txt is invalid. (22051) (SQLExecDirectW)')

Saying is can't handle '\' And it is driving me crazy. I've tried triple quote, triple double quotes, r"string", but nothing seems to work.

CODE:

import sys
import os
import pyodbc
con = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server}',server = 'SERVER', database = 'DATABASE')
cur = con.cursor()

f = open('ErrorList.txt','w')
f.write("('Station','Sequence','Script','Measurement','Fail Count')"+'\n')

f.close()
print(os.path.abspath("ErrorList.txt"))

pathtofilename = r'C:\Users\ME\Desktop\Queries\Fail w email\PythonEmail\ErrorList.txt'

sendEmail = ("EXEC msdb.dbo.sp_send_dbmail "
                "@body_format = 'TEXT', "
                "@recipients = 'reci@reci', "
                "@from_address = 'from@from' ,"
                "@reply_to = 'reply@reply', "
                "@subject = 'Test Python', "
                " @file_attachments = '" + os.path.abspath('ErrorList.txt') + "', "
                "@body = 'No Message with commit attach 2'")


cur.execute(sendEmail)
con.commit()
cur.close()
con.close()
os.remove("ErrorList.txt")
ATal
  • 21
  • 3
  • Since you're not paying attention to [injection issues](http://bobby-tables.com/): Use **PREPARED STATEMENTS** with **PLACEHOLDER VALUES**. – tadman Jan 24 '18 at 20:17
  • Have you tried adding double quotes around the string (that is, SQL Server needs the path to be quoted, because it is a UNC with spaces in it): `pathtofilename = '"' + pathtofilename + '"'` or `pathname = '"' + os.path.abspath('ErrorList.txt') + '"'` – cowbert Jan 24 '18 at 20:24
  • From every result I found for that error code, it's the same; you don't have access to that file from SQL account. You'll need to sort permissions on Windows. There's nothing in your error to suggest it's due to escaping backslash. – roganjosh Jan 24 '18 at 20:25
  • define the variable first then use it in the open statment pathtofilename = r'C:\Users\ME\Desktop\Queries\Fail w email\PythonEmail\ErrorList.txt' f = open(pathtofilename,'w') – Biz. Nigatu Jan 24 '18 at 22:06

0 Answers0