Customizing the output of a System stored proc

• vivian123

  Old Hand

  Points: 390

  More actions

  August 1, 2003 at 11:54 pm

  #162113

  Hi everybody

  How do I capture the output of a stored proc into a table[temp table/table variable]? The sp_who stored proc shows all user sessions on a database. The output includes 2 columns of interest to me - loginname and hostname. I need a list of all user(s) connecting from a particular host.

  Basically I need a subset of the columns sp_who displays [loginname & hostname], and I further need to filter the rowset based on some criteria [the hostname parameter].

  Does anybody know how to go about this?

  IMHO, if it were possible somehow to capture the output of the sp_who stored proc in a temp table/ table variable, that would solve the problem.

  All help will be appreciated.

  Thanks

  Vivian

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 2, 2003 at 6:09 pm

  #468330

  Vivian,

  Rather than capturing SP_WHO output, you could go straight into sysprocesses (like SP_WHO does anyway). The following captures all current connections from "MyPC" into a temporary table #who...

  
  
  
  
  drop table #who
  
  go
  
  select suser_sname(sid) [Login] ,hostname
  
  into #who
  
  from master..sysprocesses
  
  where hostname = 'MyPC'
  
  
  
  
  
  select * from #who 
  
  

  However, to get all connections you need to be running something continually. You could put the above code into a loop that executes every few seconds but even then there's the chance of missing some quick in-and-out connections.

  My suggestion is to schedule a trace to run on the server, capturing login events, at least login and hostname columns, and filtered on hostname. If you set the trace to run for perhaps 24 or 48 hours you'll get all connections from that hostname. Profiler can be used to save the trace file to a SQL table.

  A drawback to my suggestion is that connections with a NULL hostname will also be captured. I haven't been able to devise a method of excluding NULLs from filtered columns.

  Cheers,

  - Mark

  
  Cheers,
  - Mark