Using Machine learning Services in Sql Server

Adepu Kranthi Kumar 1 Reputation point
2021-08-18T09:11:54.937+00:00

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

124234-image.png

124253-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,561 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-08-19T01:29:18.977+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 41,021 Reputation points
    2021-08-18T10:34:35.263+00:00

    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.

    0 comments No comments

  3. Erland Sommarskog 102.2K Reputation points
    2021-08-18T21:33:35.997+00:00

    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.
    
    0 comments No comments