sp_execute and sp_prepexec in Profiler

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

sp_execute and sp_prepexec in Profiler

Author

Topic

ias0nas
Starting Member

36 Posts

Posted - 2012-08-24 : 03:41:59

Hi,

I am trying to find out the SQL executed by a third party software on our database.
Looking on the Profiler I get a lot of commands like the ones below:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 datetime2,@P2 int,@P3 varchar(4),@P4 varchar(20),
@P5 varchar(30),@P6 int,@P7 varchar(80),@P8 varchar(80),@P9 varchar(80),@P10 varchar(80),
@P11 varchar(80),@P12 varchar(80),@P13 varchar(80),@P14 varchar(80),@P15 varchar(80),
@P16 varchar(80),@P17 varchar(80),@P18 varchar(80),@P19 varchar(80),@P20 varchar(80),
@P21 varchar(80),@P22 decimal(14,3),@P23 decimal(14,3),@P24 decimal(14,3),@P25 decimal(14,3),
@P26 decimal(14,3),@P27 decimal(14,3),@P28 decimal(14,3),@P29 decimal(14,3),
@P30 decimal(14,3),@P31 decimal(14,3),@P32 decimal(14,3),@P33 decimal(14,3),@P34 decimal(14,3),
@P35 decimal(14,3),@P36 decimal(14,3),@P37 int',
N'INSERT INTO "Database_Name"."owener"."table_name" ( "report_date","report_time","company","user_identity","report_name", 
"report_line_no","x_ref_1","x_ref_2","x_ref_3","x_ref_4","x_ref_5",
"x_ref_6","x_ref_7", "x_ref_8","x_ref_9", "x_ref_10","x_ref_11","x_ref_12",
"x_ref_13","x_ref_14","x_ref_15",
"n_ref_1","n_ref_2", "n_ref_3","n_ref_4", "n_ref_5","n_ref_6","n_ref_7",
"n_ref_8","n_ref_9","n_ref_10",
"n_ref_11","n_ref_12", "n_ref_13","n_ref_14","n_ref_15",RECID) 
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13, 
@P14,@P15,@P16,@P17,@P18,@P19,@P20, @P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,
@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37)','2012-08-23 00:00:00',
77690, 'Company','user','report_name',0,'1 HeaderRecord','job_description',
'job_name','company','company',
'job_description', 'client_account','00001598','staff_name','00001598',
'staff_name','','','','' ,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,
0.000,0.000,0.000,0.000, 0.000,1256.250,0.000,NULL
select @p1

and


exec sp_execute 329,'2012-08-23 00:00:00',77690,'company','user_name',
'report_name',3,'8','job_description', 'PRO','job_stage','','01','staff_class',
'00003324','','','','','','','',0.250, 55.000,13.750,0.000,13.750,0.000,
261131.000,15.000,0.000,0.000,0.000,0.000,
0.000, 0.000,0.000,'2012-06-29 00:00:00',NULL

The third party software is populating a table with values and I would like to grab the statement it uses to populate that table.
But I can't seem to find it anywhere within the trace.
Are the records above supposed to somehow lead to the statement or am I missing something?

Thanks

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-24 : 04:00:29

It is there:

N'INSERT INTO "Database_Name"."owener"."table_name" ( "report_date","report_time","company","user_identity","report_name",
"report_line_no","x_ref_1","x_ref_2","x_ref_3","x_ref_4","x_ref_5",
"x_ref_6","x_ref_7", "x_ref_8","x_ref_9", "x_ref_10","x_ref_11","x_ref_12",
"x_ref_13","x_ref_14","x_ref_15",
"n_ref_1","n_ref_2", "n_ref_3","n_ref_4", "n_ref_5","n_ref_6","n_ref_7",
"n_ref_8","n_ref_9","n_ref_10",
"n_ref_11","n_ref_12", "n_ref_13","n_ref_14","n_ref_15",RECID)

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-24 : 04:01:07

The values being inserted are the parameters.

-Chad

ias0nas
Starting Member

36 Posts

Posted - 2012-08-24 : 04:07:17

So the point is that the SQL statement to get the results into these parameters is somewhere else or somewhere in there and I am missing it, is it?
Or can the population of the parameters with data be related (i.e. to the handle or in any other way) to the commands captured above?

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-24 : 04:14:37

It is most likely in compiled code, but not knowing the app, I can't say that is the case for sure. But you can see the values right there at the end of the statement.

ex:

@p1 = '2012-08-23 00:00:00',
@p2 = 77690

etc...

-Chad

ias0nas
Starting Member

36 Posts

Posted - 2012-08-24 : 04:25:39

By compiled code you mean a dll on the SQL server?
I can't see they are using anything like that.
The application itself is a desktop application so it's compiled but that should't matter, if the application is executing a select statement to get data it should still show in the trace, like the insert statements are, no?

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-24 : 13:13:18

No, what you are seeing is the SQL that is executing against SQL Server, they are just using sp_prepare and sp_execute to execute it. I thought you wanted to se where the parameters were actually getting set, and that is why I mentioned compiled code (Their compiled code, not SQL Server's).

-Chad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-24 : 13:50:12

What you are seeing are prepared statements and they drive me batty when running a trace on a system that has apps that use them! This is why I demand stored procedures to be used instead, and I wish my demand would be followed 100%.

And if my demand won't be followed, then I wish Microsoft would make it easier to trace prepared statements. Having to link them together is not easy and certainly monitoring tools have an issue providing data about them, such as Quest's Performance Analysis and Bill's ClearTrace. And it's not their fault, it's how the events are done in a trace.

/end rant.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources