Post reply

sp_blocker_pss80

  • March 15, 2006 at 12:43 am

    #112172

    Hi All

     

    I am running sp_blocker_pss80 SP to get the block details, but I could't able to get the FULL SQL in the output. DBCC inputbuffer truncates the SQL. so can you guys tell me how can I get the full SQL statement using this SP?

     

    Thanks in advance

    regards

    Santhosh

  • March 15, 2006 at 2:34 am

    #626700

    You could use fn_get_sql. This way the statement won't be truncated.

    DECLARE @handle binary(20)

    SELECT @handle = sql_handle

        FROM master..sysprocesses

        WHERE spid = @@SPID

    SELECT [text]

        FROM ::fn_get_sql(@handle)

     

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • March 15, 2006 at 2:39 am

    #626702

    Hi

    Thanks for the reply...

    Where should I add this inside the MS supplied sp_blocker_pss80

    procedure?

     

    regards

    Santhosh

  • March 15, 2006 at 5:22 am

    #626720

    To be honest, I haven't had alook at sp_blocker_pss80 and who to integrate fn_get_sql_handle but maybe you can find some ideas in this article. http://vyaskn.tripod.com/fn_get_sql.htm

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • March 16, 2006 at 6:01 pm

    #627186

    Hi

     

    Where can I see this fn_get_sql SP? Can I have the source code for this?

    Regards

    Santhosh

    ========================

    DECLARE @handle binary(20)

    SELECT @handle = sql_handle

        FROM master..sysprocesses

        WHERE spid = @@SPID

    SELECT [text]

        FROM ::fn_get_sql(@handle)

  • March 17, 2006 at 3:42 am

    #627224

    Santhosh,

    Exec sp_helptext 'sys.fn_get_sql'  will give you the code for this function, but I don't understand why you would need that.

    Markus

    [font="Verdana"]Markus Bohse[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply