InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:48:10 GMT20http://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxI am getting the same error @ XML.2005 - 9.00.5259.00 (Intel X86)Mon, 12 Dec 2011 07:34:03 GMTMartin Graveshttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]richa.gupta (10/10/2011)[/b][hr]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[/quote]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.Mon, 10 Oct 2011 13:16:05 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxhi - 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,RichaMon, 10 Oct 2011 12:43:56 GMTricha.guptahttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxAdded 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)...Tue, 24 May 2011 08:32:36 GMTRicky Livelyhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxThanks 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.Tue, 05 Oct 2010 11:32:13 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxJason,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!!Tue, 05 Oct 2010 11:29:56 GMTJeffrey Irishhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Adam Machanic (7/8/2010)[/b][hr][quote][b]larry.meklemburg (7/8/2010)[/b][hr]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.[/quote]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 :-)[/quote]Not a bad choice. Mine is by no means meant as a competitor to whoisactive.Thu, 08 Jul 2010 15:09:45 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]larry.meklemburg (7/8/2010)[/b][hr]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.[/quote]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 :-)Thu, 08 Jul 2010 14:17:38 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxI 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.Thu, 08 Jul 2010 14:12:15 GMTlarry.meklemburghttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]larry.meklemburg (7/8/2010)[/b][hr]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 24FOR 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[/quote]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.Thu, 08 Jul 2010 11:17:17 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxI 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 24FOR 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,LarryThu, 08 Jul 2010 11:13:35 GMTlarry.meklemburghttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Pinal Dave (6/23/2010)[/b][hr]Very very nice article Love it.[/quote]Thanks, I appreciate that.Wed, 23 Jun 2010 08:48:54 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxVery very nice article Love it.Wed, 23 Jun 2010 07:25:25 GMTPinal Davehttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Dugi (6/23/2010)[/b][hr]Jason, Thanks for the article, good job!;-)[/quote]ThanksWed, 23 Jun 2010 01:43:18 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxJason, Thanks for the article, good job!;-)Wed, 23 Jun 2010 00:54:36 GMTDugihttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxIt sounds 'simple' to add the proc parameter, but it will be pretty hard to combine the trace with the text, since I could not find it from any dm views which might hold the calling proc parameter values.Probably just an item of wish list. You have covered enough info.Could you write something more about the TaskStatus, WaitType, CPUTime, etc; and their internal relationships and impact with performance?thanks-DFri, 18 Jun 2010 10:15:42 GMTDavid Luhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]David Lu (6/17/2010)[/b][hr]Hi Jason:It works very well for me. It is in prod now, after running on QA.It almost covers everything I need to know, just one more: Is it possible to get the called proc/func parameter value in the QueryText field?:-)Thanks a lotDavid[/quote]I haven't tried that. I would typically use a trace to find that. Sounds like something to try.Thu, 17 Jun 2010 23:49:59 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]René Scharfe (6/15/2010)[/b][hr]Thanks, nice work. Just picking nits:[quote]The final bit about this procedure involves the input parameters. I chose to use TinyInts for these fields to permit a value of 0, 1, or NULL. I catch this in the where clause using case statements.[/quote]That's a case for the data type bit ([url]http://msdn.microsoft.com/en-us/library/ms177603(SQL.90).aspx[/url]). You can then use ISNULL or COALESCE when checking their values, that's shorter.Also, calling the first parameter @ShowSystemProcesses might be more intuitive, as in that case both NULL and 0 would have the same meaning.[/quote]Thanks for the feedback. Good ideas.Thu, 17 Jun 2010 23:47:46 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]John Cuthbertson (6/17/2010)[/b][hr]Sorry Adam, not Andrew!I isolated the problem thread and it displays Ok with Adam's code.<?query --FETCH API_CURSOR000000000002BC4F--?>[/quote]Thanks for the feed back. I will have to look at how he handles it. It could be useful.Thu, 17 Jun 2010 23:45:20 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxSorry Adam, not Andrew!I isolated the problem thread and it displays Ok with Adam's code.<?query --FETCH API_CURSOR000000000002BC4F--?>Thu, 17 Jun 2010 19:27:29 GMTJohn Cuthbertsonhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxjasonI isolated the problem thread and it displays Ok with Andrew's code.<?query --FETCH API_CURSOR000000000002BC4F--?>Thu, 17 Jun 2010 19:25:45 GMTJohn Cuthbertsonhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxHi Jason:It works very well for me. It is in prod now, after running on QA.It almost covers everything I need to know, just one more: Is it possible to get the called proc/func parameter value in the QueryText field?:-)Thanks a lotDavidThu, 17 Jun 2010 17:10:31 GMTDavid Luhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxHere is something that I found on the FOR XML issue that a few have mentioned:[url]http://www.sqlservercentral.com/Forums/Topic853088-149-1.aspx[/url]It seems that it may be related to unicode characters.I am curious, for those that have this issue, Adam Machanic has a proc that he listed in this thread already. Does his proc handle the FOR XML correctly for you?Tue, 15 Jun 2010 11:58:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]John Cuthbertson (6/15/2010)[/b][hr]JasonAnd I complain to my users when they do not give me enuff info!![/quote]:-D[quote]SQl Level is 9.0.4273[/quote]Thanks -Tue, 15 Jun 2010 11:53:26 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxIt worked, but I got the message:Msg 6841, Level 16, State 1, Line 7FOR 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.Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) Cheers!Tue, 15 Jun 2010 11:29:27 GMTeduardo.pinhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxThanks, nice work. Just picking nits:[quote]The final bit about this procedure involves the input parameters. I chose to use TinyInts for these fields to permit a value of 0, 1, or NULL. I catch this in the where clause using case statements.[/quote]That's a case for the data type bit ([url]http://msdn.microsoft.com/en-us/library/ms177603(SQL.90).aspx[/url]). You can then use ISNULL or COALESCE when checking their values, that's shorter.Also, calling the first parameter @ShowSystemProcesses might be more intuitive, as in that case both NULL and 0 would have the same meaning.Tue, 15 Jun 2010 09:58:16 GMTRené Scharfehttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxJasonAnd I complain to my users when they do not give me enuff info!!SQl Level is 9.0.4273Tue, 15 Jun 2010 08:57:39 GMTJohn Cuthbertsonhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Paul White NZ (6/15/2010)[/b][hr]5 stars from me. You obviously put a lot of work into this, and it shows.Loved the screenshot from 6.5 - took me way back. Simpler times.[/quote]Thanks. I thought the screenshot was the icing. It seemed the most convincing way to show the existence of who2 way back when.Tue, 15 Jun 2010 07:23:16 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx5 stars from me. You obviously put a lot of work into this, and it shows.Loved the screenshot from 6.5 - took me way back. Simpler times.Tue, 15 Jun 2010 05:03:01 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]dma-669038 (6/14/2010)[/b][hr]Very nice article Jason. I must try the code, it looks neat. I would also recommend to anyone Adam Machanic's sp_whoisactive, we use that extensively. Thanks.[/quote]Thanks.Yes - he has a very efficient and much more robust proc than my little thing.Mon, 14 Jun 2010 18:45:46 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Adam Machanic (6/14/2010)[/b][hr][quote][b]dma-669038 (6/14/2010)[/b][hr]Very nice article Jason. I must try the code, it looks neat. I would also recommend to anyone Adam Machanic's sp_whoisactive, we use that extensively. Thanks.[/quote]For reference, the URL for that is: [url]http://tinyurl.com/WhoIsActive[/url]Seems the author of this article uses it too, since he has copied my XML technique (with no attribution, and not as well as I've done, I won't hesitate to add :-P)[/quote]ermmm, nope didn't get it from WhoIsActive. However, I do like your code too. I guess I will have to look even closer at your proc.Mon, 14 Jun 2010 18:43:58 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]dma-669038 (6/14/2010)[/b][hr]Very nice article Jason. I must try the code, it looks neat. I would also recommend to anyone Adam Machanic's sp_whoisactive, we use that extensively. Thanks.[/quote]For reference, the URL for that is: [url]http://tinyurl.com/WhoIsActive[/url]Seems the author of this article uses it too, since he has copied my XML technique (with no attribution, and not as well as I've done, I won't hesitate to add :-P)Mon, 14 Jun 2010 14:41:54 GMTAdam Machanichttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxVery nice article Jason. I must try the code, it looks neat. I would also recommend to anyone Adam Machanic's sp_whoisactive, we use that extensively. Thanks.Mon, 14 Jun 2010 13:57:17 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Trey Staker (6/14/2010)[/b][hr]Nice article Jason! I haven't tested the code yet but can't wait to have a chance to do this. You did a really thorough job.[/quote]ThanksMon, 14 Jun 2010 12:49:27 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]Roy Ernest (6/14/2010)[/b][hr]Hey Jason,Good Job there. I already have this version running in QA right now. :-)[/quote]Thanks Roy.Mon, 14 Jun 2010 12:49:04 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]bitbucket-25253 (6/14/2010)[/b][hr]Jason Thanks for a job well done. Now to add this new tool to my tool box.[/quote]ThanksMon, 14 Jun 2010 12:48:43 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxNice article Jason! I haven't tested the code yet but can't wait to have a chance to do this. You did a really thorough job.Mon, 14 Jun 2010 12:20:48 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxHey Jason,Good Job there. I already have this version running in QA right now. :-)Mon, 14 Jun 2010 11:16:32 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspxJason Thanks for a job well done. Now to add this new tool to my tool box.Mon, 14 Jun 2010 11:11:58 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic936452-2650-1.aspx[quote][b]WayneS (6/14/2010)[/b][hr]Good article Jason! ;-)[/quote]Thanks Wayne.Mon, 14 Jun 2010 09:25:35 GMTSQLRNNR