I am trying to run Python in SQL Server and want to define a variable which contains the result of a simple select query.
exec sp_execute_external_script
@language = N'python', @script = N'
import pandas as pd
inventory = pd.DataFrame(sql_inventory)
',
@input_data_1 = N'SELECT top 10 * from inventory',
@input_data_1_name = N'sql_inventory'
The structure of the SQL server table (inventory) is as below:
ProductID numeric(18,3)
RegionID numeric(18,3)
ShopCode int
QTY float
Date varchar(6)
Amount float
ID bigint
But I get the below error: A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 1 An external script error occurred:
Unsupported input data type in column 'ProductID'. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary. SqlSatelliteCall error: Unsupported input data type in column 'ProductID'. Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary. 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\MSSQL14.MSSQLSERVER\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.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call ret = px_call(functionname, params) RuntimeError: revoscalepy function failed.
Should I specify data types every time I want to read from a query?