What, When, Where, How, Who2

  • Thanks much. I actually use it frequently just as a script rather than a proc as well. I see where you are coming from on that perspective.

    Thanks for the feedback and I am glad you are able to use it.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Added job names:

    ...

    ),'') AS BlockingText

    ,CASE

    WHEN LEFT(es.program_name, 31) = 'SQLAgent - TSQL JobStep (Job 0x' THEN 'SQLAgent - ' + sysjobs.[name]

    ELSE Coalesce(es.program_name,' - ')

    END as ProgramName

    ,Coalesce(es.client_interface_name,' - ') as ClientInterface

    ...

    On ec.session_id = st.session_id

    LEFT JOIN

    [msdb].[dbo].[sysjobs] sysjobs

    WITH (NOLOCK)

    ON

    CONVERT(VARCHAR(50), sysjobs.[job_id])

    = SUBSTRING(es.program_name, 38, 2)

    + SUBSTRING(es.program_name, 36, 2)

    + SUBSTRING(es.program_name, 34, 2)

    + SUBSTRING(es.program_name, 32, 2)

    + '-'

    + SUBSTRING(es.program_name, 42, 2)

    + SUBSTRING(es.program_name, 40, 2)

    + '-'

    + SUBSTRING(es.program_name, 46, 2)

    + SUBSTRING(es.program_name, 44, 2)

    + '-'

    + SUBSTRING(es.program_name, 48, 4)

    + '-'

    + SUBSTRING(es.program_name, 52, 12)

    Where es.is_user_process >= (case when @IsUserProcess = 0 Then 0 Else 1 End)

    ...

  • hi - i am also getting the same xml error. Any idea how to resolve it?

    FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

    Thanks,

    Richa

  • richa.gupta (10/10/2011)


    hi - i am also getting the same xml error. Any idea how to resolve it?

    FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

    Thanks,

    Richa

    I have not yet looked deep into the root cause of this error. I am unable to reproduce the issue. In the meantime, I'd recommend using the whoisactive script by Adam Machanic. He has put a great many hours into it and does not get the same error. Look further up the thread for the link to that script.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I am getting the same error @ XML.

    2005 - 9.00.5259.00 (Intel X86)

  • Jason,

    I too am experiencing the FOR XML error:

    Msg 6841, Level 16, State 1, Procedure jb_mywho2, Line 30

    FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

    This is on SQL Server 2012 11.0.5343.0 (x64)

    The proc has been working fine in SQL 2008R2 -

    The output rows stops at about 50% of the spids. In this instance the client seems to be an active spid owned by the "Objective" interface and the sql_text seems to be like: FETCH API_CURSOR000000000054786A

    Cheers

  • @jim

    I haven't looked at this in quite a while. I believe the issue you are encountering is resolved with a little extra code as Adam mentioned earlier in the thread. He has a proc that handles this quite nicely that most people use. It is called sp_whoisactive.

    I might tune this code up a little bit to handle that, feel free to tune it for your implementation.

    Tune into my blog to check for updates on this.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 46 through 51 (of 51 total)

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