Using Machine learning Services in Sql Server

Question

Getting error while using python as external script in stored procedure, connecting with DB, and storing the result temp table which needs to be used in next procedure as input data.

below attached is the screenshot with code and the error i am getting

Answer 1

Hi @Adepu Kranthi Kumar ,

Welcome to Microsoft Q&A!

Please have a try with below example or refer this official example and check whether any of them could be helpful to you.

CREATE PROCEDURE [dbo].get_ListOfOppToScore  
AS  
BEGIN  
  
DECLARE @input_query NVARCHAR(MAX) = N'SELECT * FROM MIP_Dev.dbo.ListOfOpportunites'  
  
EXEC sp_execute_external_script @language = N'Python', @script = N'  
import pandas as pd  
from ...  
  
# Get data from input query  
List_data = my_input_data  
  
# Define the model  
...  
  
OutputDataSet = List_data  
'  
, @input_data_1 = @input_query  
, @input_data_1_name = N'my_input_data'  
WITH RESULT SETS (("Opp_ID" varchar(100) not null,...,"DealReg" varchar(100)));  
END;  
GO  

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 2

storing the result temp table which needs to be used

External Python script runs in an external, separate process and so can't access a local temporary table.
Local temp table are only available in that SQL session where it was created.

Answer 3

The error is this line:

#TimeToClose = pandas...

In Python, # opens a comment, so that line performs nothing. Remove the hash mark.

Likewise, this line wrong:

@Output_Data_Name_1 = '#TimeToClose'

You need to remove the hash mark here as well.

Note that what you specify in the @Output_Data_name_1 is the name of the Python variable that receives the output. It has nothing to do with your temp table. The way to get data into your temp table, is to say

``´
INSERT #TimeToClose (...)
EXECUTE sp_external_script ...

Note that you cannot use WITH RESULT SETS when you have INSERT EXEC.

By the way, this was kind of an odd usage for using Python. Most people would go with a linked server. But given all the hassle and the mysterious error messages with linked server, maybe this is better.