Trace parameter values using Profiler??

  • Hi,

    I used Profiler to track all the SQL statements used by an application. I could get the SQL statements I want, but the SQL contains the variables decalred but not the actual value. Any idea how to trace those values as well?

  • Never tried. An interesting question. I will defer to Brian Kelley, he seems to be the profiler guru. Hopefully he will hit this tonight.

    Steve Jones

    steve@dkranch.net

  • I'm not sure this can be done with Profiler. You can get the execution plan and those sorts of things, but if you're are using variables LIKE @@IDENTITY and the like, the translation into an execution plan or into the show plan doesn't contain the actual value, either. I'll have to do some research.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Thanks guys.

    I'm not interested in tracking the values of SQL server pre-defined variables, like @@ROWCOUNT or @@SPID (althrough it would be great to be able to track those as well). I am interested in tracking the user-defined variable values. For example, the following SQL is provided by profiler, I wonder if I could track the values for @P1, @P2, etc.

    UPDATE dbo.S_SSA_ID

    SET

    LAST_UPD_BY = @P1,

    NEXT_SUFFIX = @P2,

    MODIFICATION_NUM = @P3,

    LAST_UPD = @P4

    WHERE

    ROW_ID = @P5 AND MODIFICATION_NUM = @P6

  • If the @Pn are user defined values, then there should be preceding events which show these values being set. Are these parameters being passed to a stored procedure or are they being set in a SQL batch query?

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Actually looking at profiler you usually see @P1 and so on get declared and set in the text. They normally only show under RPC items and text looks like this to me, you can see they are defined.

    declare @P1 int

    set @P1=180150044

    declare @P2 int

    set @P2=8

    declare @P3 int

    set @P3=1

    declare @P4 int

    set @P4=0

    exec sp_cursoropen @P1 output, N'select xp.name, cast(xp.[value] as nvarchar(4000)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''BaseType'') as nvarchar(256)), cast(SQL_VARIANT_PROPERTY(xp.[value], ''MaxLength'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Precision'') as int), cast(SQL_VARIANT_PROPERTY(xp.[value], ''Scale'') as int)from ::fn_listextendedproperty(NULL, N''user'', N''dbo'', N''table'', N''tbl2'', N''column'', N''b'') xp where xp.name not in (N''MS_Description'', N''MS_Format'', N''MS_InputMask'', N''MS_NumberOfDecimals'', N''MS_AllowBlanks'', N''MS_Caption'', N''MS_DisplayControl'', N''MS_RowSourceType'', N''MS_RowSource'', N''MS_BoundColumn'', N''MS_ColumnCount'', N''MS_ColumnHeads'', N''MS_ColumnWidths'', N''MS_ListRows'', N''MS_ListWidth'', N''MS_LimitToList'') ', @P2 output, @P3 output, @P4 output

    select @P1, @P2, @P3, @P4

  • This is generated by the server, and you'll see the parameters clearly for Cursors and prepared SQL statements.

    If you are trying to track what are truly user defined parameters you should be able to see all the variables declared in the SQL:BatchCompleted event as well.

    Mr. Travis' post reminded me that the sp_cursoropen and other internal type of SQL executions tend to use @P1, P2, ... Pn. I'm not sure if your environment parameters are declared in that fashion or with a more descriptive name such as @CustomerID or @ModelYear. However, in all cases the information should be in the TextData data column.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • They are declared as you see. I went and found them one day and forget how to tell what is what.

  • The question about how they were defined is for haidong. SQL Server type of procedures will use @P1, @P2, ... @Pn. But generally this is a bad practice for a user defined variable because @P1 doesn't tell me anything... which is why we do descriptive naming of variables in the various programming languages. Trying to get some clarification from haidong whether or not they were declaring variables that way.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • My bad, extreme appologies.

  • Thanks again.

    I incorrectly assumed that @p1...@pn are user-defined variables and that apprently caused some confusion. My apologies.

    Brian is right. My earlier trace only traced the TSQL-SQL:StmtStarting. So the only results I saw were the SQL statements with the @pn variables. I refined the trace today to include Stored Procedures-RPC:Starting and I got similar results as Antares686 posted earlier

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply