can dbcc inputbuffer show parameterized queries?

Hi,
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lc
LC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc
|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:

> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>
>
|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...[vbcol=seagreen]
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:

can dbcc inputbuffer show parameterized queries?

Hi,
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lcLC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:

> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>
>|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...[vbcol=seagreen]
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
>

can dbcc inputbuffer show parameterized queries?

Hi,
I'm trying to capture blocking information on my SQL Server 2k box and
running dbcc inputbuffer only shows me the "sp_executesql" command, it
doesn't show me the parameters which is the actual sql. Profiler seems to
have no problem showing the parameters b/c it always shows me the sql that's
running, not the sp_executesql command. Is there some other command that
will pull the sql from a parameterized query a la Profiler? Thanks for any
help here.
lcLC,
Have you tried using fn_get_sql()? (Although for sp_executesql I usually
get nothing unless a stored procedure is being executed.)
RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> Hi,
> I'm trying to capture blocking information on my SQL Server 2k box and
> running dbcc inputbuffer only shows me the "sp_executesql" command, it
> doesn't show me the parameters which is the actual sql. Profiler seems to
> have no problem showing the parameters b/c it always shows me the sql
> that's
> running, not the sp_executesql command. Is there some other command that
> will pull the sql from a parameterized query a la Profiler? Thanks for
> any
> help here.
> lc|||I've tried that function, but all of the sql handles in my databases are
0x000000... Since all of the sql handles are the same (all zero's), I'm not
getting any useful information.
"Russell Fields" wrote:
> LC,
> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
> get nothing unless a stored procedure is being executed.)
> RLF
> "LC" <LC@.discussions.microsoft.com> wrote in message
> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
> > Hi,
> > I'm trying to capture blocking information on my SQL Server 2k box and
> > running dbcc inputbuffer only shows me the "sp_executesql" command, it
> > doesn't show me the parameters which is the actual sql. Profiler seems to
> > have no problem showing the parameters b/c it always shows me the sql
> > that's
> > running, not the sp_executesql command. Is there some other command that
> > will pull the sql from a parameterized query a la Profiler? Thanks for
> > any
> > help here.
> >
> > lc
>
>|||LC
Dbcc inputbuffer shows the last command to execute through the connection.
Fn_get_sql only can work for a statement that is running _right_now_, since
as soon as a statement is complete the handle returns to 0. Therefore, most
of your sql handles should be zero, but not is a stored procedure is
actually running at the moment.
FWIW - RLF
"LC" <LC@.discussions.microsoft.com> wrote in message
news:6D105C91-1A27-413A-8117-6619CDDE76C4@.microsoft.com...
> I've tried that function, but all of the sql handles in my databases are
> 0x000000... Since all of the sql handles are the same (all zero's), I'm
> not
> getting any useful information.
> "Russell Fields" wrote:
>> LC,
>> Have you tried using fn_get_sql()? (Although for sp_executesql I usually
>> get nothing unless a stored procedure is being executed.)
>> RLF
>> "LC" <LC@.discussions.microsoft.com> wrote in message
>> news:013B24CA-D81B-48AA-9D7C-56ABA375A30D@.microsoft.com...
>> > Hi,
>> > I'm trying to capture blocking information on my SQL Server 2k box and
>> > running dbcc inputbuffer only shows me the "sp_executesql" command, it
>> > doesn't show me the parameters which is the actual sql. Profiler seems
>> > to
>> > have no problem showing the parameters b/c it always shows me the sql
>> > that's
>> > running, not the sp_executesql command. Is there some other command
>> > that
>> > will pull the sql from a parameterized query a la Profiler? Thanks for
>> > any
>> > help here.
>> >
>> > lc
>>
>>

Can a recompile lock up system tables?

One developer complained he could not use EM to see the list of UDFs,
it seemed hung. Running sp_who2 showed another developer was blocking
him. This second developer had been running a hundred lines of code
in QA, and had aborted the batch. Apparently this left the
transaction hanging open, we see that a lot. But my question is, how
did this end up blocking EM from enumerating UDFs? What else was
locked out? Is this serious, or just a problem with EM?
I speculate that SQLServer decided to recompile a UDF inside of this
transaction, and that aborting it somehow left the lock in place on
some important system table. Is this likely?
Thanks.
J.jxstern wrote:
> One developer complained he could not use EM to see the list of UDFs,
> it seemed hung. Running sp_who2 showed another developer was blocking
> him. This second developer had been running a hundred lines of code
> in QA, and had aborted the batch. Apparently this left the
> transaction hanging open, we see that a lot. But my question is, how
> did this end up blocking EM from enumerating UDFs? What else was
> locked out? Is this serious, or just a problem with EM?
> I speculate that SQLServer decided to recompile a UDF inside of this
> transaction, and that aborting it somehow left the lock in place on
> some important system table. Is this likely?
> Thanks.
> J.
If you stop a transaction from Query Analyzer, then you must issue a
rollback if you are in a transaction. If you don't, you leave the
transaction open. The procedure could have accessed a system table and
without the rollback, left the locks in place on the table.
David Gugick
Quest Software
www.imceda.com
www.quest.com