I have a Stored procedure that simply looks for Imported files through a set of other processes. The main SP runs every 5 mins and looks for new files in a directory and imports them into a staging table if there is a newer file.
At this point, if it does import a newly added file, there is another accompanying SP that ETL's the records from staging into another production DB. The ETL (
EXEC dbo.ETL) is happening inside the monitoring SP that runs every 5 minutes.
My main question is, can I have the ETL stored Procedure run independently of the monitoring SP? If an ETL SP takes longer than 5 mins, when the monitoring SP is called again, will it use another connection if the last call 5 mins ago is still running the ETL procedure?
IOW, I want to run a SP (SP #1) every 5 mins that calls other SPs (that may take more or less than 5 mins) without interrupting SP #1