How can i execute a "kill" command from inside a stored
procedure?
Thanks for your help!
by JFabi » Fri, 02 Aug 2002 03:32:38
Thanks for your help!
by Anith Se » Fri, 02 Aug 2002 03:46:21
--
- Anith
by Ken Parchinsk » Thu, 17 Oct 2002 12:47:47
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
>--
>- Anith
>.
by Andrés Taylo » Thu, 17 Oct 2002 16:44:34
HTH,
--
Andrs Taylor
http://www.sql.nu/
> 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.
3. Executing "sp_start_job" stored proc
4. DataDrivenQuery to skip a row...
5. "Errors Occured" executing a store proc
8. printing the ISQL result set
9. Stored Proc - "Optional" parameters
10. Making Stored Proc Resultset "Editable"
12. Executing a stored proc in a stored proc...