I work on sql server 2017 i add script python to import data to excel
but i get error
(1 row affected)
Msg 39004, Level 16, State 20, Line 0
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
my instance name is : AHMEDSALAHSQL
my pc name DESKTOP-L558MLK
userid sa
password 321
sql and path is local pc
import path G:\ImportExportExcel
i can access import path without any issue
so how to solve issue please ?
Error in execution. Check the output for more information.
DataStep error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "C:\PROGRA~1\MICROS~3\MSSQL1~1.AHM\MSSQL\EXTENS~1\AHMEDSALAHSQL01\5597C745-A0D5-49D6-B67F-64CC0F06E21D\sqlindb.py", line 79, in transform
rx_data_step(input_data = Output, output_file = sqlDS,overwrite = True)
File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\etl\RxDataStep.py", line 320, in rx_data_step
functionname=function_name, params=call_parameters)
File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.
SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
Rows Read: 3, Total Rows Processed: 3Caught exception in file: CxAnalysis.cpp, line: 6660. ThreadID: 3332 Rethrowing.
[Microsoft][ODBC Driver Manager] Connection not open
ODBC Error in SQLDisconnect
Caught exception in file: CxAnalysis.cpp, line: 5835. ThreadID: 3332 Rethrowing.
Caught exception in file: CxAnalysis.cpp, line: 5384. ThreadID: 3332 Rethrowing.
DataStep function failed. Please see the console output for more information.
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call
STDOUT message(s) from external script:
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.
Completion time: 2022-04-15T17:57:03.8632030+02:00
What I have tried:
declare @ImportPath NVARCHAR(MAX)='G:\ImportExportExcel'
declare @DBConnectionString NVARCHAR(MAX) = 'Database=z2data;Uid=sa;Pwd=321'
declare @ImportAll BIT=0
declare @CombineTarget BIT=0
declare @ExcelFileName NVARCHAR(200)='dbo.studentsdata'
declare @ExcelSheetName NVARCHAR(50)='students2'
--BEGIN TRY
SELECT @ImportPath = CASE WHEN RIGHT(@ImportPath,1) = '\' THEN @ImportPath ELSE CONCAT(@ImportPath,'\') END
DECLARE @Serv NVARCHAR(200) = CONCAT(CHAR(39),CHAR(39),@@SERVERNAME,CHAR(39),CHAR(39))
DECLARE @ValidPath TABLE (ValidPathCheck BIT)
INSERT @ValidPath
EXEC sp_execute_external_script
@language =N'Python',
@script=N'
import pandas as pd
d = os.path.isdir(ImportFilePath)
OutputDataSet = pd.DataFrame([d],columns=["Filename"])'
,@params = N'@ImportFilePath NVARCHAR(MAX)'
,@ImportFilePath = @ImportPath
DECLARE @PythonScript NVARCHAR(MAX) =CONCAT('
import pandas as pd
import os
import glob
from revoscalepy import RxSqlServerData, rx_data_step
sqlConnString = "Driver=AHMEDSALAHSQL;Server=Serv; ',@DBConnectionString,'"
Filefolderepath = ImportFilePath+"*.xlsx"
if ImportAll ==0:
Filename =ImportFilePath+ExcelFileName+".xlsx"
exists = os.path.isfile(Filename)
if exists and ExcelSheetName in pd.ExcelFile(Filename).sheet_names:
Output = pd.read_excel(Filename, sheetname=ExcelSheetName, na_filter=False).astype(str)
if not Output.empty:
sqlDS = RxSqlServerData(connection_string = sqlConnString,table = "".join(fl for fl in ExcelFileName if fl.isalnum())+"_"+"".join(sh for sh in ExcelSheetName if sh.isalnum()))
rx_data_step(input_data = Output, output_file = sqlDS,overwrite = True)
else:
print("Invalid Excel file or sheet name")')
--- print @PythonScript
EXEC sp_execute_external_script
@language = N'Python'
,@script = @PythonScript
,@params = N'@ImportFilePath NVARCHAR(MAX),@ImportAll BIT,@CombineTarget BIT,@ExcelFileName NVARCHAR(200),@ExcelSheetName NVARCHAR(50),@Serv NVARCHAR(200)'
,@ImportFilePath = @ImportPath
,@ImportAll = @ImportAll
,@CombineTarget = @CombineTarget
,@ExcelFileName = @ExcelFileName
,@ExcelSheetName = @ExcelSheetName
,@Serv = @Serv