Can we execute a Stored procedure(Azure SQL DB) through synapse Notebook??

Heta Desai 357 Reputation points
2022-08-23T10:54:48.037+00:00

I have created stored Procedure which has Merge Statement to perform slowly changing dimension, I would like to execute this Stored procedure from synapse notebook.

Is there any way to perform this ?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,378 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,648 questions
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator
    2022-08-24T06:36:04.787+00:00

    Hi @Anonymous ,

    Thank you for posting query in Microsoft Q&A Platform.

    You should use pyodbc library to do the same. Click here to know more about pyodbc library.

    Please check below screenshot where I tried to execute Stored procedure from Synapse notebook.
    234374-image.png

    import pyodbc  
    
    server = '<yourDbServerName>'  
    database = '<yourDBName>'  
    username = '<UserName>'  
    password = '<Password>'  
    
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE='+ database +';UID=' + username + ';PWD='+ password)  
    
    cnxn.execute('EXEC spInsertEmp')  
    cnxn.commit()  
    
    cnxn.close()  
    

    You can also consider using Synapse pipeline for this. You can execute your notebook using Synapse notebook activity and then execute Stored procedure activity to run your stored procedure.

    234298-image.png

    Hope this helps. Please let us know if any further queries.


    Please consider hitting Accept Answer button. Accepted answers help community as well.

    2 people found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.