Execute "KILL" from stored proc

Execute "KILL" from stored proc

Post by JFabi » Fri, 02 Aug 2002 03:32:38



How can i execute a "kill" command from inside a stored
procedure?

Thanks for your help!

 
 
 

Execute "KILL" from stored proc

Post by Anith Se » Fri, 02 Aug 2002 03:46:21


CREATE PROCEDURE Kill_sp

AS



GO

--
- Anith

 
 
 

Execute "KILL" from stored proc

Post by Ken Parchinsk » Thu, 17 Oct 2002 12:47:47


Anith,

I am trying to create a stored procedure that accepts a
database name as an argument and then kill all process for
that database.

I am able to identify all SPIDs to be killed; however, I
cannot reference the SPID with the following syntax.  What
am I doing wrong?  Thanks in advance.  

Regards,

Ken

USE MASTER
SELECT SPID
FROM SYSPROCESSES P, SYSDATABASES D
WHERE P.dbID = D.dbID AND D.NAME = 'PC2HP'


       KILL P.SPID
END

>-----Original Message-----
>CREATE PROCEDURE Kill_sp

>AS



>GO

>--
>- Anith

>.

 
 
 

Execute "KILL" from stored proc

Post by Andrés Taylo » Thu, 17 Oct 2002 16:44:34


Although I'm not Anith, I can tell you that this will not work. You must
create a cursor, and iterate over it, killing all spids. But if you are
running SQL Server 2000, there's a faster way, using ALTER DATABASE.

HTH,

--
Andrs Taylor


http://www.sql.nu/


> Anith,

> I am trying to create a stored procedure that accepts a
> database name as an argument and then kill all process for
> that database.

> I am able to identify all SPIDs to be killed; however, I
> cannot reference the SPID with the following syntax.  What
> am I doing wrong?  Thanks in advance.

> Regards,

> Ken

> USE MASTER
> SELECT SPID
> FROM SYSPROCESSES P, SYSDATABASES D
> WHERE P.dbID = D.dbID AND D.NAME = 'PC2HP'


>        KILL P.SPID
> END

> >-----Original Message-----
> >CREATE PROCEDURE Kill_sp

> >AS



> >GO

> >--
> >- Anith

> >.

 
 
 

1. "Errors Occured" executing a store proc

Hi,
I have a problem whitch i will describe and i hope there is someone to help
me.
I execute from my code(ADO 2.0,VC++6,SQLServer 6.5,NETLibrary:NamedPipes)
the following stored procedure with  different scenarios :

SELECT Sum(TotalValue) AS Total  FROM
ClientTx  INNER JOIN ClientTTx  ON ClientTx.TxID=ClientTTx.ID

A.Using  SQLOLEDB Provider(SQLOLEDB.1)
1.Using  CursorPosition ServerSide and asking for CursorType Keyset(Getting
CursorType            Keyset) and executing the stored procedure as
adCmdText (exec qClientStatement '0001').
It returns me a forward only cursor witch i cannot use in my code because I
expect a Keyset. .

2.Using  CursorPosition ServerSide and asking for CursorType Keyset(Getting
CursorType            Keyset) and executing the stored procedure as
adCmdStoredProc(qClientStatement ).
I get this message:   [qClientStatement   Errors Occured.]     ???
***This Works From the Microsoft ISQL/w from my client PC***

In this scenario  if  i change the stored procedure like this
CREATE PROCEDURE qClientStatement
SELECT Sum(TotalValue)  AS Total FROM
ClientTx  INNER JOIN ClientTTx  ON ClientTx.TxID=ClientTTx.ID
WHERE ClientTx.Owner='0001' (without parameter) ???
Works just fine .

Also if  make it like this

SELECT TotalValue FROM
ClientTx  INNER JOIN ClientTTx  ON ClientTx.TxID=ClientTTx.ID

works just fine.

I have also made a small  test application using only the necessary ado
commands and it  gives me for the above cases the message
"sp_procedure_params_rowset not found" ???

B.Using  SQLOLEDB ODBC Provider(MSDASQL)
1.Using  CursorPosition ServerSide and asking for CursorType Keyset(Getting
CursorType            Keyset) and executing the stored procedure as
adCmdText (exec qClientStatement '0001')
I get this message:
   [Microsoft][ODBC SQL Server Driver][SQLServer]
     Cannot open a cursor on a stored procedure that has other than a single
select statement
This happens either in Keyset or Dynamic.

2.Using  CursorPosition ServerSide and asking for CursorType Keyset(Getting
CursorType         Keyset) and executing the stored procedure as
adCmdStoredProc (qClientStatement ')
 it works just fine .

Tought it is not a solution for me , because my recordset has to be opened
and the system doesn't allow me to begin transaction in the same connection.
The message I get is "Cannot  start  transaction because more thanone  hdbc
is in use".

Is any way solving this last problem??

 I would  be vary gradegfull for an answer.

2. VB And AT Scheduler

3. Executing "sp_start_job" stored proc

4. DataDrivenQuery to skip a row...

5. "Errors Occured" executing a store proc

6. VFP - A few questions

7. Stored Proc using "like"??

8. printing the ISQL result set

9. Stored Proc - "Optional" parameters

10. Making Stored Proc Resultset "Editable"

11. max of ("...","...","..")

12. Executing a stored proc in a stored proc...

13. How to "kill" a lock