Using Machine learning Services in Sql Server

Adepu Kranthi Kumar 1 Reputation point

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



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

    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  
    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)));  

    Best regards,

    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

    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

    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