Can you increase INPUTBUFFER Size 1

[talenx]

Hello all,
I was wondering if there was a way to increase the DBCC INPUTBUFFER size, by default it's char(255).

Basically I am attempting to write a procedure that will alert me with the spid and executed SQL statement that was executed when a query runs for an extended length of time.
Currently if you where to attempt to view a running spid's SQL it only returns the first 255 chars of the statement, I need to see it all. I understand I can setup a trace but it can't be called.

Any thoughts?
Thanks,
TalenX

 

[SQLBill]

No. But Microsoft did create a new function that returns more (supposedly all of the script).

fn_get_sql

Syntax:

DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
FROM SysProcesses
WHERE SPID = 52
SELECT *
FROM ::fn_get_sql(@handle)

You change the SPID to be whatever one you want to see the command from.

This function was first created with SP3a.

-SQLBill


Posting advice: FAQ481-4875

 

[talenx]

Hi SQLBill,
Looks like it returns a char(256) result set. hmm think i'am doing something incorrect?

Cheers
Talenx

 

[SQLBill]

Are you running it in Query Analyzer and getting the results as text in QA?

If so, you have to click on Tools>Options and go to the Results tab. Change the value in "Maximum characters per column".

If you are having your results go to a file, that's not necessary.

If you are having the results appear in the grid format, you can't change it.

-SQLBill

Posting advice: FAQ481-4875

 

[talenx]

SQLBill,
VERY NICE!!!!

Thank you very much this is already coming in VERY handy , I think i will create a sp to return addtional info.

Thank you again
TalenX