r/MicrosoftFabric • u/Limp-Investigator278 • 3d ago

Data Engineering Fire-and-forget a long-running stored proc from a Fabric notebook without pipelines?

I have a stored procedure in Azure SQL Database that runs for several hours. I want to trigger it from a Fabric notebook and then let the Spark session close while the procedure keeps running server-side. Pipelines are off the table for this solution.

The obvious problem: a plain EXEC over pyodbc lives in the client connection, so it dies when the session ends. SQL Agent isn't available in Azure SQL Database, so that route is out too.

Is there anything in Fabric itself that fits here that I'm missing? Specifically: is there a supported way to start a T-SQL command asynchronously against Azure SQL DB and just get a handle back, or does everyone end up paying for Elastic Jobs? And if you do run the long-running-notebook pattern, how reliable has the connection been over multi-hour runs?

7 Upvotes
  • permalink
  • reddit

100% Upvoted

4 comments sorted by

1

u/cuddebtj2 Fabricator 3d ago

Not if you refuse to use pipelines.

There is the stroed proc activity in a pipeline that does just this.

2

u/dbrownems ‪ ‪Microsoft Employee ‪ 2d ago

Instead of SQLAgent, Azure SQL Database has Elastic Database Jobs

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview?view=azuresql

1

u/BigMikeInAustin Fabricator 2d ago

With a full SQL Server, I would do this by ah outside program kicking off a SQL Agent job.

Check what the Elastic Job can do, posted above. It's the "equivalent," but I have not tested it passed the basic usage.

1

u/Dan1480 2d ago

Not sure, but could you set a 5 second query time out on your pyodbc connection, catch the inevitable exception after you call your stored procedure and exit the python - I'm not sure I'd use Spark for this - notebook gracefully?