Python Script runs in PyCharm but Fails in Task Scheduler (Turbodbc)

Question

I have a large python script that runs just fine in PyCharm and CMD window but it keeps getting hung up and failing at the same point when I automate it through task scheduler. I have narrowed down the point in the code that it is having issues with but I cannot, for the life of me, figure out why there is a problem. I've even tried building a .bat file for the task scheduler to run but it still fails.

Code (this is the part that it keeps failing on):

import time
import turbodbc as to
import sys

logfile = "E:\\Python_Scripts\\Report\\log_file.txt"

f = open(logfile, "w")
f.write('Start - ' + str(time.ctime(time.time())) + '\n')
f.close()

# SCA SQL Server Connection
vns_odbc_driver = '{ODBC Driver 17 for SQL Server}'
vns_sql_driver = '{SQL Server Native Client 11.0}'
vns_sql_server = 'SERVER'
vns_sql_database = 'DATABASE'
vns_sql_schema = 'dbo'
vns_sql_username = 'USER'
vns_sql_password = 'PASS'
vns_sql_protocol = 'TCPIP'
vns_sql_port = '1675'

f = open(logfile, "a")
f.write(" 1. - " + str(time.ctime(time.time())) + '\n')
f.close()

try:
    vns_sql_conn = to.connect(
        driver=vns_odbc_driver,  # vns_sql_driver
        server=vns_sql_server,
        database=vns_sql_database,
        uid=vns_sql_username,
        pwd=vns_sql_password,
        protocol=vns_sql_protocol,
        port=vns_sql_port,
        autocommit=True
    )
except:
    e = sys.exc_info()[0]
    print(e)
    f = open(logfile, "a")
    f.write(" 2. " + str(e) + ' - ' + str(time.ctime(time.time())) + '\n')
    f.close()


f = open(logfile, "a")
f.write(" 3." + ' - ' + str(time.ctime(time.time())) + '\n')
f.close()

try:
    vns_sql_conn.close()
except:
    pass

f = open(logfile, "a")
f.write(" 4." + ' - ' + str(time.ctime(time.time())) + '\n')
f.close()

When I print to a log file I get the following results when run from task scheduler:

Start - Mon Jun 22 13:03:53 2020
 1. - Mon Jun 22 13:03:53 2020
 2. <class 'turbodbc.exceptions.DatabaseError'> - Mon Jun 22 13:04:09 2020
 3. - Mon Jun 22 13:04:09 2020
 4. - Mon Jun 22 13:04:09 2020

However, When I run it from a CMD window or PyCharm I get this log file:

Start - Mon Jun 22 13:03:53 2020
 1. - Mon Jun 22 13:03:53 2020
 3. - Mon Jun 22 13:04:09 2020
 4. - Mon Jun 22 13:04:09 2020

Which means it ran fine and connected to the data source. I'm thinking it must have something to do with permissions to use the drivers from task scheduler? I am logged into my server with admin privileges and have set up the task with Highest Privileges...

I think it is an issue with the permissions to use the sql native and odbc drivers. Because when I uncheck the 'Do not store password....' and put in the account password, it works just fine. The problem is that this privileged account is managed by my company's server admins and the password changes every 15 minutes. I guess that only having "access to local computer resources" does not include sql server drivers. — Mark Griggs, Jun 22 '20 at 17:52

score 1 · Answer 1 · answered Jun 23 '20 at 18:38

Try this:

In the properties tab of your scheduled task you have an "Actions" tab. Press it.

There you will have the "Start a Program" action. Press it and click the "Edit" button.

There you will see the "Program/Script:" line edit. You have to copy the path of your script and paste it into the "Start in(Optional)" line edit that is below the other one.

It would be something like this: https://drive.google.com/file/d/1unRQpa4lRAXQH_OkL4ced_5Dw6VPlxYQ/view?usp=sharing

Hope it works!

Python Script runs in PyCharm but Fails in Task Scheduler (Turbodbc)

1 Answers1