Lakehouse - Creating stored procs using Notebooks

[Soobramoney]

Hi guys,

Is it possible to create a stored proc on Fabric lakehouse from a notebook?

[Anonymous · Accepted solution]

HI @Soobramoney,

Perhaps you can use the pyodbc to connect to the data source and execute the create SP command:

import pyodbc

# copy and paste the SQL endpoint from any of the Lakehouses or Warehouses in your Fabric Workspace          
server = "your endpoint"
# copy and paste the name of the Lakehouse or Warehouse you want to connect to
database="YourDatabase"

# use Service principal to connect
clientId = ""
clientSecret = mssparkutils.credentials.getSecret("https://YourKeyVault.vault.azure.net/","your-client-secret-secret-name")

constr = f"driver=ODBC Driver 18 for SQL Server;server={server};database={database};UID={clientId};PWD={clientSecret};Authentication=ActiveDirectoryServicePrincipal;Encrypt=yes;Timeout=60;"

con = pyodbc.connect(constr)

cursor = con.cursor()

create_SP_Command=""

result=cursor.execute(create_SP_Command)

print(result)

cursor.close()
con.close()

Load data to MS Fabric Warehouse from notebook - Stack Overflow

Regards,

Xiaoxin Sheng