-- Stored Procedure: dbo.GetSQLQAUserHistory
-- Bill WUnder

use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSQLQAUserHistory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSQLQAUserHistory]
GO



CREATE PROCEDURE [dbo].[GetSQLQAUserHistory] 
AS
/*******************************************************************************************************
* admin.dbo.GetSQLQAUserHistory 
* Creator:       billw
* Date:          12-04-2003
*
* Description:   Monitor Query Analyzer Usage
* Notes:         Should not be considered all inclusive since uses point in time sampling
*
* Usage:
      EXECUTE admin.dbo.GetSQLQAUserHistory
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @ObserveDt datetime
 , @spid varchar(10)

---------------------------------------------
-- create temp tables
---------------------------------------------
declare @list table 
 (spid int
 , LastBatchDt datetime
 , login varchar(128)
 , host varchar(128)
 , NTUser varchar(128))

create table #buffer
 (EventType varchar(20)
 , Parameters int
 , EventInfo varchar(256))

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

insert @list 
 (spid
 , LastBatchDt
 , login
 , host
 , NTUser)
select distinct spid
 , last_batch
 , loginame
 , hostname
 , nt_username 
from master.dbo.sysprocesses with(nolock)
where program_name like '%Query Analyzer%'

if @@rowcount > 0
 begin
  if object_id('admin.dbo.SQLQAUserHistory','U') is null
   begin

    create table admin.dbo.SQLQAUserHistory
     (LastBatchDt datetime
     , spid int
     , login varchar(128)
     , host varchar(128)
     , NTUser varchar(128)
     , buffer varchar(256)
     , LastObservedDt datetime
     , constraint pkc_SQLQAUserHistory__LastBatchDt__spid
      primary key (LastBatchDt, spid) on [PRIMARY]) 

   end

  set @ObserveDt = getdate()

  update hist
  set LastObservedDt = @ObserveDt
  from admin.dbo.SQLQAUserHistory hist
  join @list l
  on hist.LastBatchDt = l.LastBatchDt
  and hist.spid = l.spid

  delete l
  from admin.dbo.SQLQAUserHistory hist
  join @list l
  on hist.LastBatchDt = l.LastBatchDt
  and hist.spid = l.spid

  select @spid = min(spid) from @list
  while @spid is not null
   begin
    insert #buffer exec('DBCC inputbuffer(' + @spid + ')')    

    insert admin.dbo.SQLQAUserHistory
     (LastBatchDt
     , spid
     , login
     , host
     , NTUser
     , buffer
     , LastObservedDt)
    select l.LastBatchDt
     , l.spid
     , l.login
     , l.host
     , l.NTUser
     , b.EventInfo
     , @ObserveDt 
    from @list l
    cross join #buffer b
    where l.spid = @spid
 
    delete #buffer

    select @spid = min(spid) from @list
    where spid > @spid    

   end 
 end


RETURN


GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO