-- Stored Procedure: dbo.get_sa_logins_new
-- 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].[get_sa_logins_new]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[get_sa_logins_new]
GO

CREATE procedure dbo.get_sa_logins_new
as
declare @spid int
set nocount on
/*
if object_id('sa_logins_new','U') is null
 create table admin.dbo.sa_logins_new (spid int, hostname varchar(25), program_name varchar(30), login_time datetime, inputbuffer varchar(255),
  constraint pk_sa_logins_new primary key nonclustered (spid, hostname, program_name, login_time)) 
insert sa_logins_new (spid, hostname, program_name, login_time)
select spid, left(hostname,25),left(program_name,30), login_time
from master.dbo.sysprocesses p
where program_name not like '%Agent%'
and loginame='sa'
and spid > 50
and not exists (select 1 from admin.dbo.sa_logins_new 
   where spid = p.spid
   and hostname = left(p.hostname,25)
   and program_name = left(p.program_name,30)
   and login_time = p.login_time)

create table #dbcc (EventType varchar(30), Parameters int, EventInfo varchar(255))
declare cur cursor for
select spid from admin.dbo.sa_logins_new
where inputbuffer is null
and program_name not like '%Microsoft SQL Server%'

open cur

fetch next from cur into @spid
While @@fetch_status = 0
 begin
  truncate table #dbcc
  insert #dbcc exec sp_executesql N'dbcc inputbuffer(@spid)', N'@spid int', @spid
  update admin.dbo.sa_logins_new
  set inputbuffer = (select replace(replace(EventInfo, char(13), ' '), char(10), ' ') from #dbcc)
  where spid = @spid  
  fetch next from cur into @spid
 end
close cur 
deallocate cur
*/
if object_id('WSODDataFeed_logins_new','U') is null
 create table admin.dbo.WSODDataFeed_logins_new (spid int, hostname varchar(25), program_name varchar(30), login_time datetime, inputbuffer varchar(255),
  constraint pk_WSODDataFeed_logins_new primary key nonclustered (spid, hostname, program_name, login_time)) 
insert WSODDataFeed_logins_new (spid, hostname, program_name, login_time)
select spid, left(hostname,25),left(program_name,30), login_time
from master.dbo.sysprocesses p
where program_name not like '%Agent%'
and loginame='WSODDataFeed'
and spid > 50
and not exists (select 1 from admin.dbo.WSODDataFeed_logins_new 
   where spid = p.spid
   and hostname = left(p.hostname,25)
   and program_name = left(p.program_name,30)
   and login_time = p.login_time)

create table #dbcc (EventType varchar(30), Parameters int, EventInfo varchar(255))
declare cur2 cursor for
select spid from admin.dbo.WSODDataFeed_logins_new
where inputbuffer is null
and program_name not like '%Microsoft SQL Server%'

open cur2

fetch next from cur2 into @spid
While @@fetch_status = 0
 begin
  truncate table #dbcc
  insert #dbcc exec sp_executesql N'dbcc inputbuffer(@spid)', N'@spid int', @spid
  update admin.dbo.WSODDataFeed_logins_new
  set inputbuffer = (select replace(replace(EventInfo, char(13), ' '), char(10), ' ') from #dbcc)
  where spid = @spid  
  fetch next from cur2 into @spid
 end
close cur2 
deallocate cur2
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO