I setup an SQL profiler to run, but I am not seeing any values for updates/inserts. Is there a way to see them?

All I see is stuff like this:

insert into some_tbl(id, col1, col2, col3) values ( @P0 , @P1 , @P2 )

I would love to actually see the values being inserted rather than just a generic variable, but can't seem to figure it out?

  • I think it's dependant on how you query the database, aka what you're profiling. If I use RPC:Completed, then in TextData I get the query with parameters, and then the parameters specified with value afterwards. – Allan S. Hansen Feb 19 '14 at 8:22
  • What you're seeing is a parameterized INSERT. – swasheck Apr 21 '14 at 1:20

You can select SQL:StmtStarting and SP:StmtStarting along with SQL:BatchStarting or SP:Starting

enter image description here

Better to use server-side trace rather than using GUI.

| improve this answer | |
  • Thank you for the suggestion, but using the settings you posted I got the same result. No values are given, just a bunch of P1, P2, ect – Luke Robinson Feb 18 '14 at 19:57
  • @LukeRobinson Can you post your insert statements ? Do you want the run time parameters for the variables ? – Kin Shah Feb 18 '14 at 20:08
  • The original post is pretty much all that I am seeing on update/insert operations, I have no issues with select statements though. I need to see the value of those variables that are being passed into insert/update statements instead of just the variable's name. – Luke Robinson Feb 18 '14 at 20:16

What you ate setting there is a parametrised insert or more generically use of a "prepared statement" (you will see select statements and others like this too). You might see more specific detail in the "execute prepared SQL" trace option.

If you can't see what you need anywhere in what the profiler can show you, you could try adding a "for insert" trigger to the relevant tables to log the activity, but be very careful about efficiency with this option.

| improve this answer | |

Unfortunately, there are no settings in SQL Profiler that will display the values the way you want them displayed with your insert .. values statement or any other similar statements.

But, by using SP:StmtCompleted and SQL:StmtCompleted you should be able to see when the values are being assigned to the parameters.

So you'll have an entry something like this for each parameter depending on how you're setting up these variables in your T-SQL code that you should see in the trace code.

SET @P0 = YourValue
SET @P1 = YourValue
SET @P2 = YourValue

If you want to filter it down to the parameters only then under Column Filters scroll down to TextData and add the following filters under the LIKE section:

SET @P0%
SET @P1%
SET @P2%

% being the wildcard.

| improve this answer | |
  • The above method isn't the cleanest way but it definitely shows the values which is better than nothing during debugging which is a tedious process anyways! – SQLnbe Oct 17 '14 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.