topic Re: How to execute stored procedures on synapse sql pool from databricks in Data Engineering

How to execute stored procedures on synapse sql pool from databricks

doodateika — Wed, 31 Jul 2024 12:25:55 GMT

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?

Re: How to execute stored procedures on synapse sql pool from databricks

doodateika — Fri, 02 Aug 2024 13:12:33 GMT

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! 🙂

Re: How to execute stored procedures on synapse sql pool from databricks

javiermd13 — Fri, 23 Aug 2024 04:52:14 GMT

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

Re: How to execute stored procedures on synapse sql pool from databricks

-werners- — Fri, 23 Aug 2024 06:36:32 GMT

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

use <connectiondb>;
exec <sp>

Re: How to execute stored procedures on synapse sql pool from databricks

doodateika — Fri, 23 Aug 2024 07:20:38 GMT

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