3

My Task

When user updates some values on the front end UI of an application, I want to know which SQL table/fields are updated, which SQL statements were executed, etc.

My method

Using SQL profiler, I updated some value from the applications user interface. then try to track the SQL statements executed. In Events Selection Tab, I selected all RPC & TSQL.

Problem

SQL Profiler does not show the value of incoming parameter, showing

e.g.

exec [sys].sp_describe_undeclared_parameters N'SELECT FIELD_NAME FROM EXTRA_FIELDS WHERE TABLE_NAME = @P1 AND EDITABLE = ''N'' ORDER BY CONTROL_NO, SEQNO'

Is there anyway I can get the @P1 value?

See screenshot

enter image description here

4
  • Hi, and welcome to dba.se! Please avoid uploading images for the reasons outlined in this post
    – Vérace
    Jul 4, 2021 at 11:11
  • Often time SQL Server questions are version-specific. Please add the SQL engine version tag to your question. Jul 4, 2021 at 13:10
  • 1
    Not sure what you want the profiler to show for sp_describe_undeclared_parameters the whole point is to find undeclared parameters Jul 4, 2021 at 20:10
  • SQL Profiler is kinda obsolete now (I don't know why MS still ships it). What happens if you use the far more modern (and flexible, and usable) XEvents to log it instead?
    – Dai
    Oct 3, 2022 at 9:31

1 Answer 1

2

This is the default feature of profiler unless you enable below by going into options of profiler capturing:

  1. RPC:Completed

  2. SP:StmtCompleted

  3. SQL:BatchCompleted

  4. SP:stmtstarting

  5. SQL:stmtstarting

  6. SQL:stmtcompleted

However please note that if there is nested call of procedure inside another procedure, it may not get captured.

Please go through the below set of Knowledge base concerning raised question:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c7aa2cab-f8a4-44bc-bbb5-8ef54fcd9f59/tracking-input-output-parameters-of-a-stored-proc-in-sql-server-profiler-trace?forum=sqlsearch

https://www.mytecbits.com/microsoft/sql-server/capturing-stored-procedures-parameter-values

https://www.sqlservercentral.com/forums/topic/is-there-any-way-to-capture-parameter-values-passed-for-nested-stored-procedures

You may use free scripts provided by Brent Ozar sp_BlitzCache wherein you may use cached parameter column to see the parameter of a procedure and this is not going to put any load on the server.

You may also enable extended event to capture the events instead of using profiler, which is again not going to bother your server.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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