After spending the whole night looking into and eventually fixing this problem, I thought I’d share my experiences as there doesn’t seem to be a solution from Microsoft or anywhere else on the net for this. This, and as I was stuck with this problem for over a year (on my secondary computer anyway, it worked fine on my primary and so wasn’t urgent)
If your situation is like mines, there are two issues here. Firstly, the service accounts of SQL Server and Launchpad need access to the respective drives-don’t assume and check it does, even if you think you did everything with admin access like I did.
Within the directory of C:\Program Files\Microsoft SQL Server\MSSQL14.SQL17\MSSQL\Binn is a file call pythonlauncher.config. Open that and provide access to all the folders there for those service accounts. (R integration had a similar issue but also due to how R deals with white spaces in directory names which had to be changed).
If your setup is like mines, you’ll also see a value for ENV_PYTHONPATH and this is the issue. I read some Python/Anaconda docos this setting is not worth the trouble and best avoided if necessary. This variable by the way, was created by yours truly when I first installed Python on the advice of someone on the net 🙂
Comparing the same files on other instances where it worked confirmed the differences. But simply deleting this from Environment/System Variables and removing the values from the config file may not enough. You should:
- Delete the variable from Environment/System Variables
- Reboot computer
- Repair SQL Server from the installation disk.
- Ensure all SQL and Launchpad is running
Once done, it should be fine. Note I said “should” earlier. This is because for once instance, it worked without repairing SQL while on another instance, I had to repair it. But both SQL instances now worked.
Though not related to the problem at hand, you should also ensure other configuration are correct by running this:
sp_configure ‘external scripts enabled’ , 1
RECONFIGURE WITH OVERRIDE;
The check these values by running:
sp_configure ‘external scripts enabled’
It should look like this, run_value needs to be 1.
Finally run this to see it works
EXEC sp_execute_external_script @language = N’Python’,
@script = N’print(3+4)’