How to execute SQL stored procedure in Azure Database for SQL Server using Azure Databricks Notebook

singhanuj2803 — Sun, 29 Dec 2024 16:51:28 GMT

We have Stored Procedure available in Azure Database for SQL Server and we want to call or run or execute the postgreSQL stored procedures in Azure Databricks through Notebook

We are attempting to run SQL stored procedures, through Azure Databricks notebook and would need help how to execute a stored procedure. I would need clarity in below points

What are the required libraries that needs to be installed in Databricks cluster (if any)?
How to connect with Azure Database for SQL Server using ODBC?
How to Execute SQL stored procedure in Databricks?

I am referring to below link,

https://nachiketrajput44.medium.com/how-to-run-stored-procedure-in-azure-data-warehouse-using-databricks-caa912d123d5

Let me know if anyone has step-by-step approach how to solve this issue.

Re: How to execute SQL stored procedure in Azure Database for SQL Server using Azure Databricks Note

Alberto_Umana — Sun, 29 Dec 2024 22:37:22 GMT

Hi @singhanuj2803,

To execute a SQL stored procedure in Azure Databricks, you can follow these steps:

Required Libraries:

You need to install the pyodbc library to connect to Azure SQL Database using ODBC. You can install it using the following command in a Databricks notebook cell or via cluster libraries:

%pip install pyodbc

Connecting to Azure Database for SQL Server using ODBC:

You can use the pyodbc library to establish a connection to your Azure SQL Database. Here is a Python code snippet to connect using a Service Principal:

import pyodbc

# Define the connection string

server = "your_server_name.database.windows.net"

database = "your_database_name"

clientId = "your_service_principal_client_id"

clientSecret = "your_service_principal_client_secret"

authority = "https://login.microsoftonline.com/your_tenant_id"

connection_string = (

f"Driver={{ODBC Driver 17 for SQL Server}};"

f"Server={server};"

f"Database={database};"

f"Authentication=ActiveDirectoryServicePrincipal;"

f"UID={clientId};"

f"PWD={clientSecret};"

f"Encrypt=yes;"

f"TrustServerCertificate=no;"

)

try:

# Establish the database connection

conn = pyodbc.connect(connection_string)

cursor = conn.cursor()

# Execute SQL queries here

cursor.execute("EXEC your_stored_procedure_name")

rows = cursor.fetchall()

for row in rows:

print(row)

except Exception as e:

print(f"Error: {str(e)}")

finally:

# Close the connection

conn.close()

Executing SQL Stored Procedure in Databricks:

Once you have established the connection using the above code, you can execute your stored procedure using the cursor.execute method. Replace "EXEC your_stored_procedure_name" with the actual name of your stored procedure.

Please refer to: https://learn.microsoft.com/en-us/azure/databricks/connect/external-systems/sql-server

topic Re: How to execute SQL stored procedure in Azure Database for SQL Server using Azure Databricks Note in Data Engineering

How to execute SQL stored procedure in Azure Database for SQL Server using Azure Databricks Notebook

Re: How to execute SQL stored procedure in Azure Database for SQL Server using Azure Databricks Note