sp_terminate

Syntax

server_name... sp_terminate spid [, options]

Parameters

• spid – is the thread ID, which you can obtain with the sp_who procedure or by calling srv_thread_props.
• options – determines whether the thread is terminated immediately or by a queued disconnect event:

  • Specify “deferred” to queue a disconnect event that occurs after previous events are handled. This is the default action.

  • Specify “immediate” to terminate the thread immediately, ignoring current or queued events for the thread.

Examples

• Example 1 – shows how to use isql to locate and terminate an errant server thread. The thread terminates immediately.

  1> DCSERVER...sp_who 

   2> go 

   spid  status     loginame      hostname   blk    cmd               

   ----- ---------- ------------ ---------- ----  ----------------

   1     runnable                              0    NETWORK HANDLER

   2     sleeping                              0    CONNECT HANDLER

   3     sleeping                              0    DEFERRED HANDLER

   4     runnable                              0    SCHEDULER

   12    runnable    ned           sonoma      0    PRINT TASK

   24    running     bud           sonoma      0

  
   (0 rows affected) 
  
  

• Example 2 – this DB-Library™ example queues a SRV_DISCONNECT event for the thread. The next time the thread can run, it receives the disconnect event and terminates.

  1> DCSERVER...sp_terminate 12, "immediate" 

   2> go 

   spid = 12; 

   dbrpcinit(dbproc, "sp_terminate", (DBUSMALLINT) 0); 

   dbrpcparam(dbproc, "@spid", 0, SYBINT4, -1, 

              -1, &spid); 

   dbrpcparam(dbproc, "@options", 0, SYBCHAR, 9, 

              9, "deferred"); 

   dbrpcsend(dbproc); 

Usage

Use sp_who or sp_ps to find the spid for the thread to be terminated.

Messages

spid terminated.

spid scheduled for termination.

spid not currently in use.