April 28, 2006 at 6:56 am
I've inherited a stored proc invoked by a DTS pkg and initiated via a SQL 2000 Job which is failing intermittently.
FAILURE MESSAGE: 'DETACH STAGE DB' failed with the following error: The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Only user processes can be killed.)
Q: HOW Can I exclude System SPIDs (non-User SPIDs) from my KILL process below?_______________________________________________________________________
PROCEDURE Code: The proc creates a temp table, execs sp_who2 to insert spids into the temp table, then KILLS all spids but my active task:
DECLARE @MySpid INT, @killspid INT, @strSqlStmnt NVARCHAR(4000)
SET NOCOUNT ON
CREATE TABLE #SPID
(SPID INT, Status VARCHAR(100), Login VARCHAR(100), HostName VARCHAR(100), BlkBy VARCHAR(100), DBName VARCHAR(100), Command VARCHAR(100),
CPUTime BIGINT, DiskIO BIGINT, LastBatch VARCHAR(100), ProgramName VARCHAR(100), SPID2 INT, Processed BIT DEFAULT(0))
INSERT INTO #SPID (Spid, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, Spid2)
EXECUTE SP_WHO2
--Get Active StorProc Spid
SELECT @MySpid = @@SPID
WHILE EXISTS
(SELECT 'x' FROM #SPID WHERE DBNAME = @DBName AND Spid <> @MySpid AND Processed = 0)
BEGIN
SELECT TOP 1 @killspid = Spid FROM #SPID
WHERE DBName = @DBName AND Spid <> @MySpid AND Processed = 0
ORDER BY Spid
SET @strSqlStmnt = 'Kill ' + CAST(@KillSpid AS VARCHAR(10))
EXEC sp_executesql @strSqlStmnt
UPDATE #SPID SET Processed = 1 WHERE Spid = @killspid
END
--Now, detach Database once all spids are killed. Double check connections
TRUNCATE TABLE #Spid
INSERT INTO #SPID (Spid, Status, Login, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, Spid2)
EXECUTE SP_WHO2
IF (SELECT COUNT('x') FROM #SPID WHERE DBName = @DBName AND Spid <> @MySpid AND Processed=0) = 0
BEGIN
EXEC sp_detach_db @DBName, @UpdateStats
END
DROP TABLE #SPID
April 28, 2006 at 7:35 am
First 50 spid are system spid, so remove spid < 51 from your temp table #SPID
before any other manipulations.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply