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

[singhanuj2803]

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

1. What are the required libraries that needs to be installed in Databricks cluster (if any)?
2. How to connect with Azure Database for SQL Server using ODBC?
3. 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-databr...

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

[Alberto_Umana · Accepted solution]

Hi @singhanuj2803,

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

1. 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

1. 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()

1. 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