Executing Python Script from SSMS

Question

Hi All,

I am trying to read a csv file using python in SSMS. On executing the code i am getting the following error "File not found". However, i can read the file using python command prompt editor

execute sp_execute_external_script   
@language=N'Python',  
@script=N'  
import pandas as pd  
pd.read_csv("C:\edb\csv_files\TR_data.csv")  
'  

Error Message

Msg 39004, Level 16, State 20, Line 41
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 41
An external script error occurred:

Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\7F5EC096-E9B4-4316-A5E3-16F3161DF178\sqlindb_0.py", line 33, in transform
pd.read_csv("C:\edb\csv_files\TR_data.csv")
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 678, in parser_f
return _read(filepath_or_buffer, kwds)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 440, in _read
parser = TextFileReader(filepath_or_buffer, **kwds)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 787, in init
self._make_engine(self.engine)

Msg 39019, Level 16, State 2, Line 41
An external script error occurred:
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1014, in _make_engine
self._engine = CParserWrapper(self.f, **self.options)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1708, in init
self._reader = parsers.TextReader(src, **kwds)
File "pandas_libs\parsers.pyx", line 384, in pandas._libs.parsers.TextReader.cinit
File "pandas_libs\parsers.pyx", line 695, in pandas._libs.parsers.TextReader._setup_parser_source
FileNotFoundError: File b'C:\edb\csv_files\TR_data.csv' does not exist

SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

Completion time: 2020-09-28T19:55:49.2310059-07:00

Answer 1

From the docs:

File permissions

By default, external Python and R scripts only have read access permission to their working directories.

If your Python or R scripts need access to any other directory, you need give either Read & execute and/or Write permissions to the NT Service\MSSQLLaunchpad service user account and ALL APPLICATION PACKAGES on this directory.

Follow the steps below to grant access.

In File Explorer, right click on the folder you want to use as working directory, and select Properties.
Select Security and click Edit... to change permissions.
Click Add...
Make sure the From this location is the local computer name.
Enter ALL APPLICATION PACKAGES in Enter the object names to select and click Check Names. Click OK.
Select Read & execute under the Allow column.
Select Write under the Allow column, if you want to grant write permissions.
Click OK and OK.

SQL Server 2019 on Windows: Isolation changes for Machine Learning Services - File Permissions

The reason granting folder permissions to the accounts is because it's using the Windows Mandatory Integrity Control which allows you to specify that an application runs without access to the permissions of the account it's running under.

Mandatory Integrity Control (MIC) provides a mechanism for controlling access to securable objects. This mechanism is in addition to discretionary access control and evaluates access before access checks against an object's discretionary access control list (DACL) are evaluated.

This feature was initially introduced to allow web browsers to run with less than the desktop user's security privileges.

Answer 2

Hi @Mohammad Sufian ,

Please refer below and modify your code:

EXEC sp_execute_external_script  
@language = N'Python',  
@script =  
N'  
import pandas as pd  
   
import datetime as datetime  
   
OutputDataSet = pd.read_csv("C:\sqlshack\Draft articles\Data\person.csv.bz2", names = ["PersonID", "FullName", "PreferredName", "SearchName", "IsPermittedToLogon",  "ValidFrom"],  
header = 0, compression = "bz2")  
'  
,@input_data_1 = N''  
,@input_data_1_name = N''  
WITH RESULT SETS  
(  
    (  
        PersonID INT,  
        FullName VARCHAR(512),  
        PreferredName VARCHAR(512),  
        SearchName VARCHAR(512),  
        IsPermittedToLogon bit,  
       ValidFrom nvarchar(500)  
    )  
)  
)  

Please also find more details from below:
Using Python SQL scripts for Importing Data from Compressed files

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Mohammad Sufian ,

What is your SQL Server version? You could run select @@version.

The SQL Server you are calling when executing sp_execute_external_script (SPEES), where is that installed; on your machine, or?

Don't forget when you execute SPEES it runs from the SQL box, so unless it is on your machine, it won't work. Even if it is on your machine it may not have permissions to the directory your file is in.

If the SQL is installed on your box, I suggest you create a new directory which you five EVERYONE access to and try with that directory.

Then please make sure that your account to execute SPEES has enough permisson.

Or you could have a try with small changes like below:

 pd.read_csv("C:/edb/csv_files/TR_data.csv")  
  
  pd.read_csv('C:/edb/csv_files/TR_data.csv')  
  
  pd.read_csv("C:\\edb\\csv_files\\TR_data.csv")  
  
  pd.read_csv('C:\\edb\\csv_files\\TR_data.csv')  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

The obvious question: is the file one the same machine as SQL Server are running on? If you have the file local on your disk and SQL Server runs on a different machine, it is not going to work out.

If you indeed have them on the same machine, it may be that the Launchpad account does not have permission to the file. The Python environment is a bit locked down.

Sorry, I'm short on time, so I don't have the time to try myself.

Answer 5

Hi @Mohammad Sufian ,

Please run below test to check whether the Python SQL script will process correctly in SQL Server.

execute sp_execute_external_script   
@language = N'Python',   
@script = N'  
a = 9  
b = 3  
c = a/b  
d = a*b  
print(c, d)  
'  

Besides, it could be better to grant permission to that folder or file where your csv file located.

In the SQL Server Configuration Manager, SQL Server service and the SQL Server Launchpad service should be running to use the Python scripts in the SQL Server.

We need to enable parameter external scripts enabled using the sp_configure command to run the Python SQL scripts.

EXEC sp_configure 'external scripts enabled', 1  
RECONFIGURE WITH OVERRIDE  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.