May 9, 2016 at 4:03 am
Hi we have an ETL from one DB to another on the same server but we don't want to use SSIS and we want to exec all the ETL SP's at the same time for speed of the incremental loads currently about 60 SP's this is just in developement at this time on SQL 2014
I have one file that we exec which runs fine and the load works and populates our exec monitoring table just fine 🙂
But if I do sp_who2 on the box I see a sleeping spid for each of the SP's and every time I have run it so I get loads of sleeping SPID's
My question is can I stop all the sleeping SP's am I missing a step in the code below or do I need to add some code to the SP that is being exec to stop this problem?
Declare @rc int
DECLARE @object int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)
EXEC @rc = sp_OACreate 'WScript.Shell', @object OUT
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc
RETURN
END
-- -- Add the code from above here and comment out the old stuff
EXEC @rc = sp_OAMethod @Object,'run',null,'osql -E -d DbName -S AZW-SQL-DEV-001 -q "exec dbo.Usp_Etl_Lfl_Rpt_Fact_WorkshopCapacityAndSpace"'
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT convert(varbinary(4),@RC) as result, @src as [source], @desc as [error_description]
RETURN
END
May 13, 2016 at 2:32 am
I've been trying to find an answer to this but no joy so far :ermm:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply