February 4, 2002 at 3:06 pm
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?
February 4, 2002 at 3:13 pm
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
February 4, 2002 at 3:36 pm
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 4:10 pm
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
February 5, 2002 at 2:15 pm
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 6:03 am
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
February 8, 2002 at 3:35 pm
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 4:13 pm
They are declared as you see. I went and found them one day and forget how to tell what is what.
February 8, 2002 at 4:17 pm
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 4:43 pm
My bad, extreme appologies.
February 11, 2002 at 3:51 pm
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