-- Stored Procedure: dbo.GetNamedPipesConnections -- 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].[GetNamedPipesConnections]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetNamedPipesConnections] GO CREATE PROCEDURE [dbo].[GetNamedPipesConnections] AS /******************************************************************************************************* * admin.dbo.GetNamedPipesConnections * Creator: Billw * Date: 10-22-2003 * * Description: get info about named pipe connections, used to track DBLIB connections * * Usage: EXECUTE admin.dbo.GetNamedPipesConnections select * from admin.dbo.NamedPipesLog * * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ --------------------------------------------- -- declare variables --------------------------------------------- declare @id int , @OBJStr nvarchar(1000) , @Object varchar(30) , @GetSQLStr nvarchar(1000) , @Handle varbinary(20) , @sql varchar(7500) , @spid int --------------------------------------------- -- create temp tables --------------------------------------------- declare @info table (Id int identity(1,1) primary key , SPID int , DBName varchar(18) , hostname varchar(30) , program_name varchar(50) , loginame varchar(30) , login_time datetime , last_batch datetime , sql_handle varbinary(20) , stmt_start varchar(10) , stmt_end varchar(10) , sql varchar(7500)) create table #inputbuffer (EventType varchar(20) , Parameter int , EventInfo varchar(255)) --------------------------------------------- -- set session variables --------------------------------------------- SET NOCOUNT ON if object_id('dbo.NamedPipesLog','U') is null create table admin.dbo.NamedPipesLog (SPID int , DBName varchar(18) , HostName varchar(30) , Application varchar(50) , Login varchar(30) , LoginTime datetime , LastBatch datetime , sql varchar(7500) , constraint pk_NamedPipesLog__LastBatch__SPID primary key (LastBatch, SPID)) --------------------------------------------- -- body of stored procedure --------------------------------------------- insert @info (SPID , DBName , hostname , program_name , loginame , login_time , last_batch , sql_handle , stmt_start , stmt_end) select spid , db_name(dbid) , hostname , program_name , loginame , login_time , last_batch , sql_handle , stmt_start , stmt_end from master.dbo.sysprocesses with(nolock) where spid <> @@SPID and net_library = 'Named Pipes' select @id = min(id) from @info i where not exists (select 1 from admin.dbo.NamedPipesLog where SPID = i.spid and LastBatch = i.last_batch) while @id is not null begin select @GetSQLStr = 'select @sql = replace(replace(replace(substring(text, ' + '(' + stmt_start + ' + 2)/2,CASE ' + stmt_end + ' ' + 'WHEN -1 THEN (datalength(text)) ' + 'ELSE (' + stmt_end + ' - ' + stmt_start + ' + 2)/2 ' + 'END) ,char(13),char(32)),char(10),char(32)),char(9),char(32)) ' + 'from ::fn_get_sql(@Handle)' , @Handle = sql_handle from @info where id = @id and stmt_end > 0 if @GetSQLStr is not null exec sp_executesql @GetSQLStr, N'@Handle varbinary(20), @sql varchar(7500) OUTPUT', @Handle, @sql OUTPUT else begin select @spid = spid from @info where id = @id insert #inputbuffer exec sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@spid select @sql = replace(replace(EventInfo, char(13), ' '), char(10), ' ') -- get rid of CR and LF from #inputbuffer if @sql is null set @sql = 'not available' end update i2 set sql = @sql from @info i1 join @info i2 on i1.SPID = i2.SPID where i1.Id = @id insert admin.dbo.NamedPipesLog (SPID , DBName , HostName , Application , Login , LoginTime , LastBatch , sql) select SPID , DBName , HostName , program_name , loginame , login_time , last_batch , sql from @info i where id = @id select @id = min(id) from @info i where not exists (select 1 from admin.dbo.NamedPipesLog where SPID = i.spid and LastBatch = i.last_batch) and id > @id end RETURN GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[GetNamedPipesConnections] TO [NT AUTHORITY\ANONYMOUS LOGON] GO