Summary:
I've setup a python script (simplified in this case, removing all ODBC/SQL statements) that creates two log files in a child directory. I then created a exe file off of this named TestApp.exe using PyInstaller. Manually running the executable as the SQL server agent user works. When the exe file is setup as a job step in SQL server, using the CmdExec function, the process hangs, and will not close outside of using the task manager.
I get a perpetually spinning "Start jobs" green circle on the "Execute job 'TestApp'"
Finding out why the batch file is 'running' successfully without executing the exe file, and finding out why the exe file when pointed to directly, is hanging, is my goal.
Local virtual box is running Windows 10
Windows Server 2016 is being used
SQL server is version 13.0.5292.0
Python 3.6.6 is being used.
PyInstaller 3.5 is being used.
Stuff I've Tried:
I have several other EXE files that execute VB/shell code that are working successfully in the same directory, with the exact same setup.
I've attempted to use the windows task scheduler to run this exe file, and am getting the same results.
I've looked at changing the path to cause different errors (which then causes the job to error correctly). Changing the user owner to a separate admin user with different permissions setups causes the job to fail correctly.
I've attempted to run as separate admin users manually without the SQL job, and those work.
I've attempted to run the exe file as a batch file job:
start E:\SQLLoadFiles\TestApp\TestApp.exe
The above batch file causes the job to succeed, but then the exe file never runs. The batch file, when run manually, (with a different directory pointer, as the E:\ drive is specific to the SQL server agent user), it runs successfully without issue.
I've attempted simplifying the script down to just create simple files (which will be seen below).
I've attempted remoting in as the SQL server agent and running the executable manually in the same E:\ drive location, and it executes successfully without issue.
I've ensured that pyinstaller isn't setup to show a console, with the --noconsole option, as I've heard that can cause issues when running in the background.
One last note: when the step is hanging, history for the step/job doesn't show the job as running.
SQL Setup for job/step:
Type: Operating system (CmdExec)
Run As: SQL Server Agent Service Account
On success: Quit job reporting success
On Failure: Quit the job reporting failure
SQL CmdExec step command: E:\LoadingFiles\TestApp\TestApp.exe
Or in batch format: E:\LoadingFiles\TestApp\Starter.exe
Batch file code: start E:\LoadingFiles\TestApp\TestApp.exe
Python Code:
#Quite a few libraries removed
import datetime, csv, os.path, datetime
#Setup Logging here
now=datetime.datetime.now()
logfilename='FBlog_' + str(now.strftime('%Y-%m-%d_%H-%M-%S'))
#print(logfilename) \#removed as part of removing any console visibility
logfb=open('logs/%s.txt' % logfilename, 'w+')
startstring='Starting run ' + str(now.strftime('%Y-%m-%d_%H:%M:%S')) + '\n'
logfb.write(str(startstring))
logfb.close()
##########Application build code for pyinstaller:############
import PyInstaller.__main__
PyInstaller.__main__.run([
'--name=%s' % 'TestApp',
'--onefile',
'--noconsole',
'--clean',
'TestApp.py'
])
I expect either an error message to fire, or for the application to begin, create the log file, and then finish; closing down the process.
Discovered the issue. Apparently has to do with the user context of the SQL server agent, and I needed to hard-set the 'start-in' or working directory. That option is not available for CmdExec, so I switched the CmdExec over to Powershell:
Start E:\LoadingFiles\TestApp\TestApp.exe -wo E:\LoadingFiles\TestApp
This fixes the issue. The job was hanging due to python trying to access a non-existing directory due to the working directory being set to '\' (or something, I can't find the actual default working directory).