Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What, When, Where, How, Who2


What, When, Where, How, Who2

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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

larry.meklemburg
larry.meklemburg
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
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.
Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1141 Visits: 714
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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

Jeffrey Irish
Jeffrey Irish
SSC-Enthusiastic
SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)SSC-Enthusiastic (163 reputation)

Group: General Forum Members
Points: 163 Visits: 1122
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 Pinch

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 w00t
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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

Ricky Lively
Ricky Lively
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 1707
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)
...
richa.gupta
richa.gupta
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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

Martin Graves
Martin Graves
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 252
I am getting the same error @ XML.

2005 - 9.00.5259.00 (Intel X86)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search