Run a stored procedure from fabric notebook

[SurajKakde]

Hey Everyone, greetings

Our ETL is combination of notebooks and t-sql procedures. We are trying to log any info/error message into a warehouse table and this is done using a t sql procedure. I am trying to find a way to run stored procedure through notebook.

Please let me know if this is possible. 

Thank you

[Anonymous]

Hi @SurajKakde ,

Why not use warehouse to run stored procedures directly, it's a simple alternative.

If you have the ODBC Driver installed, you can also try running the following code in your notebook.

import pyodbc

# Connection string
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=your_server_name;"
    "DATABASE=your_dbname;"
    "UID=db_username;"
    "PWD=db_pwd"
)

# Connection
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

stored_procedure = "{CALL your_stored_procedure}"
params = (param1, param2)

# Execute it
cursor.execute(stored_procedure, params)

# Get results if available
results = cursor.fetchall()
for row in results:
    print(row)

# Close it
cursor.close()
conn.close()

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[SurajKakde]

Hi Yang

Thank you for the response.

I have Bronze and Silver layer as lakehouse and Gold as warehouse. Gold layer has tables to capture ETL runs and error/log messages using stored procedure. Notebooks are used for lakehouse and t-sql for warehouse. So I am trying to log run details of notebook into warehouse tables by call the procedure from notebook.

I problem I see is I don't see any means to use sql string to connect warehoues and excute procedure in notebook and warehouse doesn't have user name / password.

Thank you

[Anonymous · Accepted solution]

Hi @SurajKakde ,

Try running the following code in a notebook:

import pyodbc
import mssparkutils

# Copy and paste SQL endpoints from warehouse            
server = "your_endpoint"
# Copy and paste the name of the warehouse
database = "YourDatabase"

# Connect
clientId = "your_client_id"
clientSecret = mssparkutils.credentials.getSecret("https://YourKeyVault.vault.azure.net/", "your-client-secret-secret-name")
tenantId = "your_tenant_id"

constr = (
    f"Driver={{ODBC Driver 18 for SQL Server}};"
    f"Server={server};"
    f"Database={database};"
    f"UID={clientId};"
    f"PWD={clientSecret};"
    f"Authentication=ActiveDirectoryServicePrincipal;"
    f"Encrypt=yes;"
    f"TrustServerCertificate=no;"
    f"Connection Timeout=30;"
)

con = pyodbc.connect(constr)

cursor = con.cursor()

# Stored procedure command
create_SP_Command = "EXEC YourStoredProcedureName"

# Execute it
result = cursor.execute(create_SP_Command)

cursor.close()
con.close()

Here's a case with a situation very similar to yours, which I'm sure will help:

Load data to MS Fabric Warehouse from notebook - Stack Overflow

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!