dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profilerPossibly fn_get_sql can be of help. There are some things to think about whe
n using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:

> Possibly fn_get_sql can be of help. There are some things to think about w
hen using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
>
>

dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profilerPossibly fn_get_sql can be of help. There are some things to think about when using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:
> Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> > Hello all,
> >
> > Is there a way to display (using TSQL) the actual statement that
> > a SPID is executing?
> >
> > When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> >
> > Thanks
> >
> > PS: I cannot use the profiler
>
>

dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profiler
Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler
|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:

> Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
>
>

DBCC Inputbuffer work around...

Hello,
I want to be able to give a couple of my junior dbas the ability to do a
DBCC INPUTBUFFER. However, they do not belong to the symin role. Is
there a way that I can give them the ability to execute the DBCC INPUTBUFFER
without making them members of the symin role? If there is a way... how
would I go about getting it accomplished.
I created a stored procedure and pass the spid as the parameter and execute
the whole statement as a string... but that still did not work. I am using
SQL Server 2000.
Please advise...
Thank you,
BrettAs BOL says, "symin fixed server role only".
AMB
"Brett Davis" wrote:

> Hello,
> I want to be able to give a couple of my junior dbas the ability to do a
> DBCC INPUTBUFFER. However, they do not belong to the symin role. Is
> there a way that I can give them the ability to execute the DBCC INPUTBUFF
ER
> without making them members of the symin role? If there is a way... ho
w
> would I go about getting it accomplished.
> I created a stored procedure and pass the spid as the parameter and execut
e
> the whole statement as a string... but that still did not work. I am usin
g
> SQL Server 2000.
> Please advise...
> Thank you,
> Brett
>
>
>|||Brett Davis wrote:
> Hello,
> I want to be able to give a couple of my junior dbas the ability to
> do a DBCC INPUTBUFFER. However, they do not belong to the symin
> role. Is there a way that I can give them the ability to execute the
> DBCC INPUTBUFFER without making them members of the symin role? If
> there is a way... how would I go about getting it accomplished.
> I created a stored procedure and pass the spid as the parameter and
> execute the whole statement as a string... but that still did not
> work. I am using SQL Server 2000.
> Please advise...
> Thank you,
> Brett
You could use xp_cmdshell from the SP and execute OSQL and have it log
into the server using Windows Authentication and then return the results
from the DBCC INPUTBUFFER.
For example (using the current SPID which you'll need to change for the
actualy query):
exec master..xp_cmdshell N'OSQL.EXE -E -q"dbcc inputbuffer(@.@.spid)"'
David Gugick
Imceda Software
www.imceda.com|||Brett,
You could create a COM+ object that has symin rights and store it on your
middle-tier server. By writing a web application to interface to the COM
object, you could grant select users access to the web page that calls the
COM object (but not the COM object itself).
Russell Fields
"Brett Davis" <bdavis123@.cox.net> wrote in message
news:%23Zddz$hCFHA.2632@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I want to be able to give a couple of my junior dbas the ability to do a
> DBCC INPUTBUFFER. However, they do not belong to the symin role. Is
> there a way that I can give them the ability to execute the DBCC
INPUTBUFFER
> without making them members of the symin role? If there is a way...
how
> would I go about getting it accomplished.
> I created a stored procedure and pass the spid as the parameter and
execute
> the whole statement as a string... but that still did not work. I am
using
> SQL Server 2000.
> Please advise...
> Thank you,
> Brett
>
>

dbcc inputbuffer value

Hi,
I want to store DBCC inputbuffer value in a variable and then want to
comapare the value.
How ca i store the value in a variable or table inside a script?You can try this...
create table #temp (eventType varchar(100),parameters int, EventInfo
nvarchar(200))
insert into #temp exec('dbcc inputbuffer (' + @.@.spid + ')')
select * from #temp
drop table #temp
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

DBCC InputBuffer statement to be part of a select statement

I want to use DBCC inputbuffer function for all the spids in sysprocesses table without using a cursor or loop. In other words, I want to have the value of dbcc inputbuffer part of the select statement directly or indirectly. Is there a way??SQLDBAxxx,
Try running the following:

----------
set nocount on
select 'exec DBCC INPUTBUFFER('+convert(varchar,spid)+')'
from sysprocesses
go
----------

This will generate the DBCC code you need to run; it won't run
DBCC in the TSQL mode that you need. You can save the result set
and reload it to run it. Simple, but works...

I've never been able to execute a DBCC statement using ANSI-SQL syntax( select,update, insert ).|||SQLDBAxxx,
Try running the following:

----------
set nocount on
select 'DBCC INPUTBUFFER('+convert(varchar,spid)+')'
from sysprocesses
go
----------

This will generate the DBCC code you need to run; it won't run
DBCC in the TSQL mode that you need. You can save the result set
and reload it to run it. Simple, but works...

I've never been able to execute a DBCC statement using ANSI-SQL syntax( select,update, insert ).|||Thank you Scooter. But this is not what I wanted to do. I want to capture the input buffer for a given spid. I can do it thro a cursor or loop but if my recordset is big then by the time it comes to execute the dbcc inputbuffer for the last spid the inputbuffer may have changed. That is the reason I wanted to try by some other way to narrow down the time difference.

dbcc inputbuffer equivalent in SQL 2005

I know dbcc inputbuffer still exists in 2005, but what are the other ways to
check the underlying SQL running ?
btw, is dbcc inputbuffer a backward compatibility feature or is it still the
preferred command on 2005 ?
btw, what about sp_who2 ? Does the underlying command use the DMVs ?
fn_get_sql can be used
Take a look into the usage:
http://msdn2.microsoft.com/en-us/library/ms189451.aspx
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>
|||Hi Hassan
The new metadata objects in SQL 2005 are the sys.dm_exec_query_stats view,
and the sys.dm_exec_sql_text function. The follow query gets all the
sql_handles from the view, and uses them with the function to return the
text of the statement:
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
If you want to see how a stored procedure is written, you can look at the
definition for yourself:
SELECT object_definition(object_id('sp_who'))
You can also look at the definition of any DMV, catalog view or
compatability view using the object_definition function.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>

dbcc inputbuffer equivalent in SQL 2005

I know dbcc inputbuffer still exists in 2005, but what are the other ways to
check the underlying SQL running ?
btw, is dbcc inputbuffer a backward compatibility feature or is it still the
preferred command on 2005 ?
btw, what about sp_who2 ? Does the underlying command use the DMVs ?fn_get_sql can be used
Take a look into the usage:
http://msdn2.microsoft.com/en-us/library/ms189451.aspx
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>|||Hi Hassan
The new metadata objects in SQL 2005 are the sys.dm_exec_query_stats view,
and the sys.dm_exec_sql_text function. The follow query gets all the
sql_handles from the view, and uses them with the function to return the
text of the statement:
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
If you want to see how a stored procedure is written, you can look at the
definition for yourself:
SELECT object_definition(object_id('sp_who'))
You can also look at the definition of any DMV, catalog view or
compatability view using the object_definition function.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>

dbcc inputbuffer equivalent in SQL 2005

I know dbcc inputbuffer still exists in 2005, but what are the other ways to
check the underlying SQL running ?
btw, is dbcc inputbuffer a backward compatibility feature or is it still the
preferred command on 2005 ?
btw, what about sp_who2 ? Does the underlying command use the DMVs ?fn_get_sql can be used
Take a look into the usage:
http://msdn2.microsoft.com/en-us/library/ms189451.aspx
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>|||Hi Hassan
The new metadata objects in SQL 2005 are the sys.dm_exec_query_stats view,
and the sys.dm_exec_sql_text function. The follow query gets all the
sql_handles from the view, and uses them with the function to return the
text of the statement:
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
If you want to see how a stored procedure is written, you can look at the
definition for yourself:
SELECT object_definition(object_id('sp_who'))
You can also look at the definition of any DMV, catalog view or
compatability view using the object_definition function.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OJoEmv0FHHA.4580@.TK2MSFTNGP05.phx.gbl...
>I know dbcc inputbuffer still exists in 2005, but what are the other ways
>to check the underlying SQL running ?
> btw, is dbcc inputbuffer a backward compatibility feature or is it still
> the preferred command on 2005 ?
> btw, what about sp_who2 ? Does the underlying command use the DMVs ?
>

dbcc inputbuffer does not list parameters

I have a lot of sprocs being called but a dbcc inputbuffer on the spid only
lists the sproc name without the parameters being passed. Why is that ?
Using SQL 2000 and SP3a. ThanksHassan
I did some testing in the QA
exec spMySP '%spinsert%'
EventType Parameters EventInfo
-- -- ----
--
Language Event 0 spMySP '%spinsert%'
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
>I have a lot of sprocs being called but a dbcc inputbuffer on the spid only
>lists the sproc name without the parameters being passed. Why is that ?
>Using SQL 2000 and SP3a. Thanks
>|||Try this:
1)Execute the relevant sproc from Query Analyzer within the correct db
2)identify the @.@.spid used - through a SELECT @.@.spid
3)Open another window with Query Analyzer - make sure the db is set at
MASTER . execute dbcc inputbuffer(@.@.spid) --from step 2
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
> I have a lot of sprocs being called but a dbcc inputbuffer on the spid
only
> lists the sproc name without the parameters being passed. Why is that ?
> Using SQL 2000 and SP3a. Thanks
>|||These are for RPC calls made from our application.
If I run the trace, i see the parameters called but not through dbcc
inputbuffer
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:ds76pr$74s$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Try this:
> 1)Execute the relevant sproc from Query Analyzer within the correct db
> 2)identify the @.@.spid used - through a SELECT @.@.spid
> 3)Open another window with Query Analyzer - make sure the db is set at
> MASTER . execute dbcc inputbuffer(@.@.spid) --from step 2
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
> only
>

dbcc inputbuffer does not list parameters

I have a lot of sprocs being called but a dbcc inputbuffer on the spid only
lists the sproc name without the parameters being passed. Why is that ?
Using SQL 2000 and SP3a. ThanksHassan
I did some testing in the QA
exec spMySP '%spinsert%'
EventType Parameters EventInfo
-- -- ----
Language Event 0 spMySP '%spinsert%'
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
>I have a lot of sprocs being called but a dbcc inputbuffer on the spid only
>lists the sproc name without the parameters being passed. Why is that ?
>Using SQL 2000 and SP3a. Thanks
>|||Try this:
1)Execute the relevant sproc from Query Analyzer within the correct db
2)identify the @.@.spid used - through a SELECT @.@.spid
3)Open another window with Query Analyzer - make sure the db is set at
MASTER . execute dbcc inputbuffer(@.@.spid) --from step 2
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
> I have a lot of sprocs being called but a dbcc inputbuffer on the spid
only
> lists the sproc name without the parameters being passed. Why is that ?
> Using SQL 2000 and SP3a. Thanks
>|||These are for RPC calls made from our application.
If I run the trace, i see the parameters called but not through dbcc
inputbuffer
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:ds76pr$74s$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Try this:
> 1)Execute the relevant sproc from Query Analyzer within the correct db
> 2)identify the @.@.spid used - through a SELECT @.@.spid
> 3)Open another window with Query Analyzer - make sure the db is set at
> MASTER . execute dbcc inputbuffer(@.@.spid) --from step 2
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
>> I have a lot of sprocs being called but a dbcc inputbuffer on the spid
> only
>> lists the sproc name without the parameters being passed. Why is that ?
>> Using SQL 2000 and SP3a. Thanks
>>
>

dbcc inputbuffer does not list parameters

I have a lot of sprocs being called but a dbcc inputbuffer on the spid only
lists the sproc name without the parameters being passed. Why is that ?
Using SQL 2000 and SP3a. Thanks
Hassan
I did some testing in the QA
exec spMySP '%spinsert%'
EventType Parameters EventInfo
-- -- ----
Language Event 0 spMySP '%spinsert%'
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
>I have a lot of sprocs being called but a dbcc inputbuffer on the spid only
>lists the sproc name without the parameters being passed. Why is that ?
>Using SQL 2000 and SP3a. Thanks
>
|||Try this:
1)Execute the relevant sproc from Query Analyzer within the correct db
2)identify the @.@.spid used - through a SELECT @.@.spid
3)Open another window with Query Analyzer - make sure the db is set at
MASTER . execute dbcc inputbuffer(@.@.spid) --from step 2
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Hassan" <Hassan@.hotmail.com> wrote in message
news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
> I have a lot of sprocs being called but a dbcc inputbuffer on the spid
only
> lists the sproc name without the parameters being passed. Why is that ?
> Using SQL 2000 and SP3a. Thanks
>
|||These are for RPC calls made from our application.
If I run the trace, i see the parameters called but not through dbcc
inputbuffer
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:ds76pr$74s$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Try this:
> 1)Execute the relevant sproc from Query Analyzer within the correct db
> 2)identify the @.@.spid used - through a SELECT @.@.spid
> 3)Open another window with Query Analyzer - make sure the db is set at
> MASTER . execute dbcc inputbuffer(@.@.spid) --from step 2
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:O7sSjLtKGHA.524@.TK2MSFTNGP09.phx.gbl...
> only
>

dbcc inputbuffer and long commands

We have some commands here that go over 2k characters, and I've seen
some elsewhere with text values that went over 64k, but dbcc
inputbuffer only seems to return 256 characters. Is there any way to
fetch longer commands?
Thanks.
Josh
On Tue, 07 Dec 2004 10:28:45 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>We have some commands here that go over 2k characters, and I've seen
>some elsewhere with text values that went over 64k, but dbcc
>inputbuffer only seems to return 256 characters. Is there any way to
>fetch longer commands?
Aha, found:
http://support.microsoft.com/default...b;en-us;325607
FIX: The fn_get_sql Function Returns SQL Text for Handle in the
Sysprocesses System Table
But it doesn't seem to be very robust - seems not to find the command
in cache all the time.
At least the fn/hotfix is present on a SQL2K sp3a system, fwiw.
J.
|||jxstern wrote:
> We have some commands here that go over 2k characters, and I've seen
> some elsewhere with text values that went over 64k, but dbcc
> inputbuffer only seems to return 256 characters. Is there any way to
> fetch longer commands?
> Thanks.
> Josh
DBCC INPUTBUFFER returns an nvarchar(255)
You might be able to use fn_get_sql. See BOL for more info.
David Gugick
Imceda Software
www.imceda.com

dbcc inputbuffer and long commands

We have some commands here that go over 2k characters, and I've seen
some elsewhere with text values that went over 64k, but dbcc
inputbuffer only seems to return 256 characters. Is there any way to
fetch longer commands?
Thanks.
JoshOn Tue, 07 Dec 2004 10:28:45 -0800, jxstern <jxstern@.nowhere.com>
wrote:
>We have some commands here that go over 2k characters, and I've seen
>some elsewhere with text values that went over 64k, but dbcc
>inputbuffer only seems to return 256 characters. Is there any way to
>fetch longer commands?
Aha, found:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325607
FIX: The fn_get_sql Function Returns SQL Text for Handle in the
Sysprocesses System Table
But it doesn't seem to be very robust - seems not to find the command
in cache all the time.
At least the fn/hotfix is present on a SQL2K sp3a system, fwiw.
J.|||jxstern wrote:
> We have some commands here that go over 2k characters, and I've seen
> some elsewhere with text values that went over 64k, but dbcc
> inputbuffer only seems to return 256 characters. Is there any way to
> fetch longer commands?
> Thanks.
> Josh
DBCC INPUTBUFFER returns an nvarchar(255)
You might be able to use fn_get_sql. See BOL for more info.
David Gugick
Imceda Software
www.imceda.com

Dbcc Inputbuffer

Hi folks.
DBCC INPUTBUFFER(79)
the statement returned is too short, not full. Can i get full STATEMENT that is sent by the user.
Howdy!If you are using sp3a see "What's New" in the release notes. Otherwise, no.

-PatP|||Hi Pat, thanx.
select serverproperty('productlevel')
returns SP3.
There are BOOKSONLINE in the programs group only. Where to find the release notes.

Howdy!|||There is a new function in SP3 called fn_get_sql.

Here is an article:

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-14-fn_get_sql.htm

HTH,
Roby2222|||If you check the release notes for sp3a (http://www.microsoft.com/sql/downloads/2000/sp3.asp?SD=gn&LN=en-us&gssnb=1), you'll find a reference to fn_get_sql (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_3nqa.asp) that ought to solve your problem.

-PatP|||Thanx all.
But there's a problem.
i can get the STATEMENT only for SPIDs that r currently running. Not for those which have status SLEEPING.
DBCC INPUTBUFFER returns the last statment for SLEEPING connections but ::fn_get_sql doesn't.

eg. if i open QA , execute STATEMENT and don't disconnect, i can get the text from DBCCINPUTBUFFER but not from the function.

Howdy!