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

Question

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

Heta Desai 357

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 ?

Subashri Vasudevan 11,231 Reputation points

2022-08-23T12:11:19.153+00:00

Hi @Anonymous ,

Is there any reason to use notebook? You have stored proc activity in synapse pipeline which can be used for calling an sp.
Heta Desai 357 Reputation points

2022-08-23T12:38:48.777+00:00

Yes.I performng incremental load where using notebook performing data transformation and storing transformed data into Azure SQL staging are. to load data from staging to actual warehouse model created stored procedure with MERGE statement to perform slowly changing dimension. I want to perform two step process in one execution.
Subashri Vasudevan 11,231 Reputation points

2022-08-23T12:43:39.28+00:00

Please check this SO thread, where they have used pyodbc library to execute SQL commands. Similar way you can try running stored procs.

Accepted answer

0 additional answers

Your answer

Subashri Vasudevan 11,231 Reputation points

2022-08-23T12:11:19.153+00:00

Hi @Anonymous ,

Is there any reason to use notebook? You have stored proc activity in synapse pipeline which can be used for calling an sp.
Heta Desai 357 Reputation points

2022-08-23T12:38:48.777+00:00

Yes.I performng incremental load where using notebook performing data transformation and storing transformed data into Azure SQL staging are. to load data from staging to actual warehouse model created stored procedure with MERGE statement to perform slowly changing dimension. I want to perform two step process in one execution.
Subashri Vasudevan 11,231 Reputation points

2022-08-23T12:43:39.28+00:00

Please check this SO thread, where they have used pyodbc library to execute SQL commands. Similar way you can try running stored procs.

Answer 1

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.

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.

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

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

Share via

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

0 additional answers

Your answer