SEO

#1 (**permalink**) 04-24-2008, 06:08 PM

(apologies for the cross posting but 5 days with no joy in other
newsgroup)

Hi
I'm sure lots of people have asked this before, but I can't find
any
threads so:

Assuming you haven't had your profiler open for long enough is there
any way of getting to the procedure
code that was prepared with sp_prepexec in query analyser?
If not why not? (I'm sure theres a good reason but I'd like to
know)

i.e. something in syscomments or similiar?

thanks

C

#2 (**permalink**) 04-25-2008, 12:42 AM

C,

Syscomments is for code that is persisted in a SQL Server database, such as
a stored procedure, function, view, etc. Prepared statements are not to be
found there.

There is a system table/view available in 2000 and 2005 that may help you in
some cases:
select * from master.dbo.syscacheobjects

This is deprecated in SQL Server 2005, so you should investigate the new
dynamic management views for 2005. This page from the BOL has a link to a
discussion of the new views that you should be using:
http://msdn2.microsoft.com/en-us/library/ms187815.aspx

RLF

<codefragment@googlemail.com> wrote in message
news:bd4ad615-e8fa-453f-b72f-bfee5d874e25@a23g2000hsc.googlegroups.com...
> (apologies for the cross posting but 5 days with no joy in other
> newsgroup)
>
> Hi
> I'm sure lots of people have asked this before, but I can't find
> any
> threads so:
>
> Assuming you haven't had your profiler open for long enough is there
> any way of getting to the procedure
> code that was prepared with sp_prepexec in query analyser?
> If not why not? (I'm sure theres a good reason but I'd like to
> know)
>
> i.e. something in syscomments or similiar?
>
>
> thanks
>
>
> C

#3 (**permalink**) 05-02-2008, 05:04 AM

> * * select * from master.dbo.syscacheobjects

Thats great thanks. I'm on (as the customers as on) SQL 2000, not SP3

Your reply lead me to this post, it seems this is trivial in 2005 or
2000 SP3.

http://www.sqlservercentral.com/Foru...301-146-1.aspx

Now if I can just find the missing link in SQL 2000 between what
profiler says and this table
I'll be happy :-) Assuming thats possible of course :-|

Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

sp_prepexec and sys_comments