How to execute stored procedures on synapse sql pool from databricks

[doodateika]

In the current version of databricks, previous methods to execute stored procedures seem to fail. 

spark.sparkContext._gateway.jvm.java.sql.DriverManager/

spark._sc._gateway.jvm.java.sql.DriverManager returns that it is JVM dependent and will not work. Using pyodbc does not currently like it used to because drivers must be installed and using init scripts at DBFS seems to be deprecated in addition to authentication passthrough for storing the init scripts at ABFSS. So my question is: What is the current way to execute synapse stored procedures from databricks? Do I have it wrong and the methods above actually work? Is there some new method that i have missed in the docs?

[Kaniz_Fatma]

Hi @doodateika, You can use the built-in JDBC connector with OAuth2 authentication to execute stored procedures. Another approach is to use Databricks SQL Warehouses to execute your stored procedures. 

[doodateika]

Hi thanks for responding, how would i go about doing that? Do you have anything with more detail? Im glad to try whatever suggestions you have! 🙂

[javiermd13]

I'm facing the same issue @Kaniz_Fatma , is there a working solution to execute a stored procedure? 

[doodateika · Accepted solution]

Hi, my issue was "resolved" when i switched to personal compute with 14.3 LTS ML Runtime. OAuth did not seem to make a difference since i still needed to use drivermanager at the end of the day. So if you need a temporary solution i would suggest trying that, but i do hope that there are plans for fixing this in the future.

# Write your SQL statement as a string
statement = """TRUNCATE TABLE dbo.test"""

# Fetch the driver manager from your spark context
driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager

# Create a connection object using a jdbc-url, + sql uname & pass
con = driver_manager.getConnection(jdbc_url, user, password)

# Create callable statement and execute it
exec_statement = con.prepareCall(statement)
exec_statement.execute()

# Close connections
exec_statement.close()
con.close()

[-werners-]

can you create a connection to external data in unity catalog, and then:

use <connectiondb>;
exec <sp>