October 14, 2009 at 5:16 pm
Hello everyone,
I have a rather complicated question about SQL Server that I am hoping someone can help me with. Below is a brief overview of the problem.
I am working on a logging solution for our ETL to load some of our reporting tables. I am trying to create a logging process that requires as little effort as possible to implement across hundreds of stored procedures. One of the goals of this project is too capture both the both the names and values of the parameters that are passed to the stored procedures.
Below is the structure of a sample stored procedure that I would like to audit, with some pseudo code:
Here is the audit log:
USE [AIG_LOG_DATA]
GO
/****** Object: Table [dbo].[log_data_table_sample] Script Date: 10/14/2009 17:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[log_data_table_sample](
[log_data_table_sample_id] [int] IDENTITY(1,1) NOT NULL,
[parameter_names_values] [varchar](1000) NULL,
CONSTRAINT [PK_log_data_table_sample] PRIMARY KEY CLUSTERED
(
[log_data_table_sample_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Here is a sample stored procedure to audit:
create proc sample_proc_to_audit @ltemp as varchar(50)
as
insert into dbo.log_data_table_sample (parameter_names_values)
/* the value of the paramaters I would like to audit… I would like to make this dynamic so that we don’t have to enter unique select statements for each stored procedure… in other words I do not want to have select @ltemp */
The names of the parameters can be accessed using the following query:
SELECT PARAMETER_NAME
from information_schema.parameters
where SPECIFIC_NAME = ‘sample_proc_to_audit’
I can access the names of the paramaters; however the values of the parameters remain elusive. How do I determine the values of the stored procedure paramaters without directly selecting from them (i.e. SELECT @ltemp)? I have tried other strategies such as dynamic sql, however this does not seem to work. I think it has something to do scope.
Any help with this would be most appreciated.
Thanks,
PB
October 19, 2009 at 6:37 am
I'd love to be able to do this too. However, I've yet to find a reliable way to do it. I've tried fn_get_sql and the dynamic management views without success. I don't know enough about .Net to try to build a CLR call that might accomplish it.
I'm responding here so that this issue will bubble up to the top again. Maybe one of the heavy hitters on the site knows how to do it or can verify that it can't be done with TSQL.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply