October 5, 2010 at 11:32 am
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
May 24, 2011 at 8:32 am
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)
...
October 10, 2011 at 12:43 pm
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
October 10, 2011 at 1:16 pm
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
December 12, 2011 at 7:34 am
I am getting the same error @ XML.
2005 - 9.00.5259.00 (Intel X86)
April 20, 2016 at 7:52 pm
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
April 21, 2016 at 10:46 am
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