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

Question

We have Stored Procedure available in Azure Database for SQL Server and we want to call or run or execute the SQL 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 Server 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.

Answer 1

The libraries you need :

• pyodbc: for ODBC connections
• Azure Active Directory Authentication Library (adal) or msal: If you are using Azure Active Directory authentication
• pandas: to be able to work with data frames (optional, but useful)

To install these:

1. Go to your Databricks workspace.
2. Navigate to Clusters > Select your cluster.
3. Click on Libraries > Install New.
4. Install the following libraries:
   • PyPI: Install pyodbc, pandas, adal, or msal.

To connect to Azure Database for SQL Server, you need an ODBC driver. Databricks provides a built-in driver for this so depending on your authentication type:

• SQL Authentication:

driver = "{ODBC Driver 17 for SQL Server}"
server = "your-server.database.windows.net"
database = "your-database-name"
username = "your-username"
password = "your-password"

• Azure Active Directory Authentication :

conn_str = f"DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password};"

How to execute the stored SQL procedure :

import pyodbc
# Connection details
driver = "{ODBC Driver 17 for SQL Server}"
server = "your-server.database.windows.net"
database = "your-database-name"
username = "your-username"
password = "your-password"
# Connection string
conn_str = f"DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password};"
# Connect to the database
connection = pyodbc.connect(conn_str)
# Create a cursor
cursor = connection.cursor()
# Execute the stored procedure
stored_procedure = "EXEC YourStoredProcedureName @param1 = ?, @param2 = ?"
params = (value1, value2)  # Replace with your actual parameter values
cursor.execute(stored_procedure, params)
# Commit the transaction (if required)
connection.commit()
# Fetch results (if the procedure returns any)
results = cursor.fetchall()
for row in results:
    print(row)
# Close the connection
cursor.close()
connection.close()

Links to help you :

https://learn.microsoft.com/en-us/answers/questions/818990/can-we-execute-a-stored-procedure%28azure-sql-db%29-th

https://community.databricks.com/t5/data-engineering/sql-stored-procedure-in-databricks/td-p/26817

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

https://www.mssqltips.com/sqlservertip/6151/using-azure-databricks-to-query-azure-sql-database/

Answer 2

You can consider the following function, which we use to execute queries against PostgreSQL.

import psycopg2

def run_script_in_postgres(query):

"""

Executes the given SQL query against a PostgreSQL database.

Args:

query: The SQL query to be executed.

Raises:

Exception: If an error occurs during the connection or execution.

"""

try:

connection = psycopg2.connect(

    host=POSTGRES_HOST,

    user=POSTGRES_USERNAME,

    port=POSTGRES_PORT,

    password=POSTGRES_PASSWORD,

    dbname=POSTGRES_DATABASE

)

cursor = connection.cursor()

cursor.execute(query)

connection.commit()

except (Exception, psycopg2.Error) as error:

print("Error while connecting to PostgreSQL", error)

raise

finally:

if connection:

  cursor.close()

  connection.close()

Example usage:

sql_query = "SELECT * FROM your_table;"

run_script_in_postgres(sql_query)