May 29, 2013 at 12:07 pm
I am new to sql profiler, below is the output of a sql profiler trace and I have questions listed for each event captured. Looking for clarity. Thanks in advance
Dean-O
When this one executes, does it pass the value of @p1 to the next RPC?
If not, what does it do with it?
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=54
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1
What does it do with @p1, @p3, @p4?
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=56
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4
What do these last three do?
They run select statements, but what for?
What do they do with the data they select?
Event Class = SQL: BatchCompleted
TextData = SELECT pe.enterprise_id, pe.practice_id, pe.person_id, pe.other_id_number, pe.first_name, pe.last_name, pe.middle_name, pa.person_id ,pa.med_rec_nbr, pe.date_of_birth, pe.sex, pe.nickname FROM { oj person pe LEFT OUTER JOIN patient pa ON pe.person_id = pa.person_id AND pa.practice_id = '0001' } WHERE pe.person_id = '557D78F4-F830-47C6-BE87-23F567F2FC3C'
---------------------------------------------------------------------------------------
Event Class = SQL: BatchCompleted
TextData = INSERT INTO ngn_ids( id_name, id_value, in_update ) VALUES ( 'Histories_MedSurg', '557D78F4-F830-47C6-BE87-23F567F2FC3C', 1 )
---------------------------------------------------------------------------------------
Event Class = SQL: BatchCompleted
TextData = DELETE FROM ngn_ids WHERE id_name = 'Histories_MedSurg' AND id_value = '557D78F4-F830-47C6-BE87-23F567F2FC3C'
Why does it show Trace Start here?
Shouldn't this be at the start of the trace?
Event Class = Trace Start
---------------------------------------------------------------------------------------
Event Class = Trace Stop
May 29, 2013 at 12:22 pm
Applications using OLE DB/ODBC/ADO.NET can use prepared sql statements and that's what sp_prepexec is essentially. The output parameter is the handle for the prepared sql statement so that if the application runs the same statement many times it can just use the handle when calling sp_execute although usually you will see sp_prepare followed by sp_execute, not sp_prepexec.
For the second one:
sp_prepexecrpc is used by ODBC to call a stored procedure. I'm guessing the 3 variables you mention are additional parameters required for the stored procedure, ng_update_person.
The next 3 questions are a bit harder to answer especially since only 1 is a select and the other 2 are an insert and a delete. I can't tell you what the application does with the results of the select statement. I assume it displays the data in some form to the user.
The last questions about why trace start shows later would probably be because the results are not ordered by time, but by event class.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 1:03 pm
Thanks for pointing out I had these events sorted by type of event instead of event start time. I sorted them by event start time, so they make a little more sense... but still have some questions...
Event Class = Trace Start
---------------------------------------------------------------------------------------
What does this do?
Event Class = RPC: Completed
TextData = exec sp_unprepare 26
---------------------------------------------------------------------------------------
Does select @p1 actually execute the SQL statement or does it get the returned value of the sql statement?
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=26
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1
---------------------------------------------
I don't understand the select @p1, @p3, @p4
It has executed the SQL, so @p1 stores the result of the SQL, right?
is select @p1, @p3, @p4 actually a select statement? if not what is it doing in that select line?
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=28
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4
---------------------------------------------
What does this do?
Runs a select statement, but what for?
What does it do with the data selected?
Event Class = SQL: BatchCompleted
TextData = SELECT pe.enterprise_id, pe.practice_id, pe.person_id, pe.other_id_number, pe.first_name, pe.last_name, pe.middle_name, pa.person_id ,pa.med_rec_nbr, pe.date_of_birth, pe.sex, pe.nickname FROM { oj person pe LEFT OUTER JOIN patient pa ON pe.person_id = pa.person_id AND pa.practice_id = '0001' } WHERE pe.person_id = '557D78F4-F830-47C6-BE87-23F567F2FC3C'
_____________________________________
May 29, 2013 at 1:24 pm
rochesterd (5/29/2013)
Thanks for pointing out I had these events sorted by type of event instead of event start time. I sorted them by event start time, so they make a little more sense... but still have some questions...Event Class = Trace Start
---------------------------------------------------------------------------------------
What does this do?
Event Class = RPC: Completed
TextData = exec sp_unprepare 26
---------------------------------------------------------------------------------------
This removes the prepared sql statement from the SQL Server. You should see one of these for every sp_prepexec or sp_prepare/sp_execute combination, unless you end the trace before a prepared sql statement is no longer needed.
Does select @p1 actually execute the SQL statement or does it get the returned value of the sql statement?
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=26
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1
---------------------------------------------
Select @p1 is just returning the value to the application so the application can re-use it with another sp_execute or with sp_unprepare
I don't understand the select @p1, @p3, @p4
It has executed the SQL, so @p1 stores the result of the SQL, right?
is select @p1, @p3, @p4 actually a select statement? if not what is it doing in that select line?
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=28
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4
---------------------------------------------
@p1 is the handle for the sql statement, @p3 and @p4 are output parameters for the call and are returned so the application can use them if needed.
What does this do?
Runs a select statement, but what for?
What does it do with the data selected?
Event Class = SQL: BatchCompleted
TextData = SELECT pe.enterprise_id, pe.practice_id, pe.person_id, pe.other_id_number, pe.first_name, pe.last_name, pe.middle_name, pa.person_id ,pa.med_rec_nbr, pe.date_of_birth, pe.sex, pe.nickname FROM { oj person pe LEFT OUTER JOIN patient pa ON pe.person_id = pa.person_id AND pa.practice_id = '0001' } WHERE pe.person_id = '557D78F4-F830-47C6-BE87-23F567F2FC3C'
_____________________________________
This is a select statement being run by an application so that a user can get data about the person specified in the where clause. What the application/user does with the data isn't something anyone on the forum can know because we don't have the application. If you want to know what the application does with the data you need to get a copy of the application, use it, and see what happens in the application when this select is run, work with a person who uses the application regularly to find out what is done with the data, or talk to the vendor/app devs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2013 at 1:34 pm
Excellent, I think I am getting the sql profiler speak...
just for clarity...
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=26
exec sp_prepexec @p1 output,NULL,N'SELECT in_update FROM ngn_ids WHERE id_name = ''Histories_MedSurg'' AND id_value = ''557D78F4-F830-47C6-BE87-23F567F2FC3C'''
select @p1
You wrote: Select @p1 is just returning the value to the application so the application can re-use it with another sp_execute or with sp_unprepare
So since this was a select statement, @p1 is just a handle to the sql select statement, not the value of result set of in_update values? is the result set of values just sent automatically back to the calling application?
-------------------------------------------------------------------
Event Class = RPC: Completed
TextData = declare @p1 int
set @p1=28
declare @p3 int
set @p3=0
declare @p4 varchar(max)
set @p4='ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
exec sp_prepexecrpc @p1 output,N'ng_update_person',@po_result_code=@p3 output,@po_result_message=@p4 output,@pi_person_id='557D78F4-F830-47C6-BE87-23F567F2FC3C',@pi_user_id=154
select @p1, @p3, @p4
You wrote:@p1 is the handle for the sql statement, @p3 and @p4 are output parameters for the call and are returned so the application can use them if needed.
So @p1 is the handle again, not the returned value of zero or rows updated, right?
And is @p3 zero
and is @p4 'ng_update_person: (Success), Person "Deano TestRochester" {557D78F4-F830-47C6-BE87-23F567F2FC3C} Added.'
which are both sent back to the calling application?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply