Profiler trace bind variables

CSC-Benefit



Hi,

Could anyne help me on which options in Profiler could be used to trace the bind variables in DML

Given a table, X (col1 number, col2 varchar(10)), where col1 is primary key.

Example DML statements:

1) insert into X values (@parameter1, @parameter2)
2) update X set col2 = @parameter2 where col1 = @parameter1
3) delete X where col1 = @parameter1

where @parameter1 and @parameter2 are bind variables.

Can Profiler be configured to log the actual values of @parameter1 and @parameter2 in the example statements in trace log

Thanks a lot.

Arnie Rowland

As I indicated in your previous thread, Profiler will provide you the opportunity to log the entire SQL statement, replacing the parameters with the values.

For @parameter1 = 25, @parameter2 = 3;

insert into x values ( 25, 3 )

etc.

And I still think you are 'barking up the wrong tree' to seriously be considering Profiler as a Auditing tool.

CSC-Benefit

Sorry, I guess I am confusing you a little
In order to give my colleagues a complete answer, I'm now trying to answer a question if SQL Profiler could be possibly used as an alternative auditing solution at extreme conditions, without considering the performance or system resource cost, etc. Your suggestions and comments on Profiler not a good solution for auditing is completely accepted without any reservation.

My sample code looks like the followings, and I started all options under T-SQL.
In Profiler, I only see the update statement being logged as "update xx set col2 = 'CCCC' where col1 = @xxx"
If the actual values of @xxx can not be logged in trace file, it means I would not be able to figure out which row exactly being updated, perticularly, in more complicated algorithms.

If my observation is true, then I can reach to a conclusion that SQL Profile can not be used as an alternative auditing tool in any condition 'cause it doesn't give us the completed information about value changes, even if we'd like to suffer on performance in an extreme condition.

However, I am not 100% sure if I missed anything in SQL Profiler that could somehow show the values of bind variable.
Again, very sorry for asking so many questions :-)

Here are what I tested,
Given a table xx (col1 int, col2 varchar(20), I have code:

DECLARE @xxx AS INT;
DECLARE C CURSOR FAST_FORWARD FOR SELECT col1 FROM dbo.xx;
OPEN C
FETCH NEXT FROM C INTO @xxx WHILE @@fetch_status = 0
BEGIN
-- Process data here
update xx set col2 = 'CCCC' where col1 = @xxx;
FETCH NEXT FROM C INTO @xxx;
update xx set col2 = 'CCCC' where col1 = @xxx;
END
CLOSE C;
DEALLOCATE C;

Arnie Rowland

Profiler will capture the statement as presented to SQL Server. So when using a SQL client tool's query window, declaring and setting variables are separate statements. And as you see, the action statement includes the variable or parameter.

However, your application will replace the application variable (or control.text) with the actual value when it sends the statement to SQL Server. (SQL Server would not have privy to the variable declaration and value assignment on the application side.) Profiler will capture the statement with the actual values when sent from the application.

In order to use Profiler as a auditing tool, all SQL statements must come from the client. As you have noticed, stored procedures, functions, and scripts present significant problems for an attempt at auditing.

It's good that you are asking questions and fleshing out the reasons that Profiler doesn't make a good auditing tool.

CSC-Benefit

Thanks a lot for your valuable comments, I believe I got the answers. Thanks again