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
