Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

What, When, Where, How, Who2 Expand / Collapse
Author
Message
Posted Thursday, July 8, 2010 11:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 18,064, Visits: 16,106
larry.meklemburg (7/8/2010)
I too get the error, but not every time that I run it. If I catch the culprit, I will let you know.

I am using 9.00.4262.00 (X64).

The error is;
Msg 6841, Level 16, State 1, Line 24
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.

Great article and very useful.
Thanks,
Larry


Thanks for the note. Do you have anything like what John demonstrated? I would recommend trying Adam's script when this occurs. It should work and illustrate where the problem is. I am planning on comparing the two to determine how to avoid this error.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #949464
Posted Thursday, July 8, 2010 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, January 5, 2014 1:05 PM
Points: 39, Visits: 15
I believe it is exactly what occurs in John's case. Thanks for the idea. I will try running Adam's whoisactive script when I next get the error.

As a note, I just ran your script and the error did not occur. I will keep you informed and thanks.
Post #949579
Posted Thursday, July 8, 2010 2:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:51 PM
Points: 1,113, Visits: 707
larry.meklemburg (7/8/2010)
I believe it is exactly what occurs in John's case. Thanks for the idea. I will try running Adam's whoisactive script when I next get the error.

As a note, I just ran your script and the error did not occur. I will keep you informed and thanks.


Not trying to overshadow the work that Jason has done, but why not just stick with Who is Active? It doesn't have the error and returns all of the same data plus a lot more


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #949581
Posted Thursday, July 8, 2010 3:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 18,064, Visits: 16,106
Adam Machanic (7/8/2010)
larry.meklemburg (7/8/2010)
I believe it is exactly what occurs in John's case. Thanks for the idea. I will try running Adam's whoisactive script when I next get the error.

As a note, I just ran your script and the error did not occur. I will keep you informed and thanks.


Not trying to overshadow the work that Jason has done, but why not just stick with Who is Active? It doesn't have the error and returns all of the same data plus a lot more


Not a bad choice. Mine is by no means meant as a competitor to whoisactive.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #949617
Posted Tuesday, October 5, 2010 11:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:55 PM
Points: 149, Visits: 1,030
Jason,

I have used this to great effect! However, rather than use it as a stored proc, I just run the query to get the information.

I'm sure you're asking why. Mainly because I support many databases that I am not the direct DBA for. If I am troubleshooting, this is one of the tools in my toolbox.

Recently I have been getting errors and it took me a while to figure out just what the heck I did. Basically, if you run this script against any database other than Master, it fails with all manner of nifty errors

I just added "USE Master" at the top so that I don't make that mistake again.

In anycase, thanks a ton for this handy tool!!


Regards,

Irish
Post #998601
Posted Tuesday, October 5, 2010 11:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 18,064, Visits: 16,106
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
Post #998604
Posted Tuesday, May 24, 2011 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 10, Visits: 1,099
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)
...
Post #1114077
Posted Monday, October 10, 2011 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 5, 2013 1:53 PM
Points: 16, Visits: 282
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
Post #1188076
Posted Monday, October 10, 2011 1:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 18,064, Visits: 16,106
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
Post #1188091
Posted Monday, December 12, 2011 7:34 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 11:58 AM
Points: 8, Visits: 187
I am getting the same error @ XML.

2005 - 9.00.5259.00 (Intel X86)
Post #1220252
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse