November 3, 2009 at 2:48 pm
Sorry must be brain dead or blind, can someone tell me what is wrong with the sql below
DECLARE @p_SPID int
declare @p_SQL varchar(400)
DECLARE dbcursor CURSOR FOR
SELECT SPID FROM master.dbo.sysprocesses WHERE dbid = 16 AND SPID > 50
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @p_SPID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID )
- PRINT @p_SQL
EXECUTE( @p_SQL )
FETCH NEXT FROM dbcursor INTO @p_SPID
END
CLOSE dbcurosr
DEALLOCATE dbcursor
November 3, 2009 at 2:55 pm
I had to delete your indents and fix a typo in the Close command, but after that it compiled and ran on my machine.
There weren't any SPIDs to close, since I don't have a DBID 16. I modified it to a database that I do have, and just used the print command instead of the execute command, and that worked just fine.
What problem are you running into on it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 3:04 pm
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '?'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'CLOSE'
November 3, 2009 at 3:15 pm
Delete the indents.
Are you using an editor other than Management Studio for this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 3:18 pm
OK I give up, removing indents worked
November 4, 2009 at 7:48 am
It means something other than the ASCII standard tab character or spaces was used. That's why I asked about what editor you're using.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply