Listing prepared statements...

Hi...
I've noticed when using Microsoft SQL Profiler, when prepared statements are
in use, yields only such information as "sp_execute 3, 12, 34" etc. This
contrasts with using stored procedures, whereby I get the entire "Select"
statement in the profiler output.
I know if I enable events in the profiler, such as TSQL>Exec Prepared SQL
and TSQL>Prepare SQL, I get to see the initial preparation of the statement,
and I'm able to resolve statement handle does what. (For example, in my
example above, what does statement '3' actually do?)
But what about if I start the profiler AFTER the statements have been
prepared (and assigned to their various numeric handle numbers)?
Is there a query or method by which I can display all currently-prepared SQL
statements?
Thanks for any help,
Mark.
Mark wrote:
> Hi...
> I've noticed when using Microsoft SQL Profiler, when prepared
> statements are in use, yields only such information as "sp_execute 3,
> 12, 34" etc. This contrasts with using stored procedures, whereby I
> get the entire "Select" statement in the profiler output.
> I know if I enable events in the profiler, such as TSQL>Exec Prepared
> SQL and TSQL>Prepare SQL, I get to see the initial preparation of the
> statement, and I'm able to resolve statement handle does what. (For
> example, in my example above, what does statement '3' actually do?)
> But what about if I start the profiler AFTER the statements have been
> prepared (and assigned to their various numeric handle numbers)?
> Is there a query or method by which I can display all
> currently-prepared SQL statements?
> Thanks for any help,
> Mark.
You might be able to have a look in master..syscacheobjects. Try
filtering on the objtype column for "Prepared statement".
David Gugick
Quest Software
www.quest.com
|||Thanks David,
I'll give it a try.
Mark.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OEuWHctIGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Mark wrote:
> You might be able to have a look in master..syscacheobjects. Try
> filtering on the objtype column for "Prepared statement".
>
> --
> David Gugick
> Quest Software
> www.quest.com
>
|||David,
Again, thank you very much, fantastic! Yes, you're right, there in
syscacheobjects is the SQL statement and dbid related to prepared
statements.
Mark.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OEuWHctIGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Mark wrote:
> You might be able to have a look in master..syscacheobjects. Try
> filtering on the objtype column for "Prepared statement".
>
> --
> David Gugick
> Quest Software
> www.quest.com
>

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me
Some examples here:
http://vyaskn.tripod.com/passing_arr...procedures.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me
|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

Lisen Port

Hello,
I've an aplication make with Apache TomCat that uses the SQL as a DB motor,
i've installed the MSDE version.
Now the Apache couldn't conect with SQL, i think that the problem is in the
conection port, because i created an ODBC conection (in the server machine)
and it only runs property when i mark the option "detect port automaticly",
when i indicated the 1433 port the conection fails. In the apache
configuration i must put the conection port (pe. MYSERVER:1433), i tried
with the port 1433 but it don't works.
could somebody help me?
thanks
hi Tolo,
Tolo wrote:
> Hello,
> I've an aplication make with Apache TomCat that uses the SQL as a DB
> motor, i've installed the MSDE version.
> Now the Apache couldn't conect with SQL, i think that the problem is
> in the conection port, because i created an ODBC conection (in the
> server machine) and it only runs property when i mark the option
> "detect port automaticly", when i indicated the 1433 port the
> conection fails. In the apache configuration i must put the conection
> port (pe. MYSERVER:1433), i tried with the port 1433 but it don't
> works.
> could somebody help me?
> thanks
if you installed a default instance of MSDE it will listen on TCP/IP 1433
port, where named instances usually use dynamically assigned ports, those
requiring UDP 1434 port to be available for the SQL Server Resolution
Service, which will then report the appropriate network endpoints to use for
the particular SQL Server instance, redirecting the MDAC (requires MDAC 2.6
or above) client connections to the appropriate TCP port
you can however set a fixed port using the Server Network Utility
(svrnetcn.exe) and specifying the preferred port (TCP/IP 1433 in your case)
and thus resolving your problem...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Solved,
Thanks, thanks, thanks :D
best reggards,
Tolo
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> escribi en el mensaje
news:36pe05F52ncdvU1@.individual.net...
> hi Tolo,
> Tolo wrote:
> if you installed a default instance of MSDE it will listen on TCP/IP 1433
> port, where named instances usually use dynamically assigned ports, those
> requiring UDP 1434 port to be available for the SQL Server Resolution
> Service, which will then report the appropriate network endpoints to use
for
> the particular SQL Server instance, redirecting the MDAC (requires MDAC
2.6
> or above) client connections to the appropriate TCP port
> you can however set a fixed port using the Server Network Utility
> (svrnetcn.exe) and specifying the preferred port (TCP/IP 1433 in your
case)
> and thus resolving your problem...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>