amachanic
commented
5 years ago I've heard about this now from a few different places. Does anyone know how to repro it?
Closed bbworks closed 3 years ago
amachanic
commented
5 years ago I've heard about this now from a few different places. Does anyone know how to repro it?
wdcadmin
commented
5 years ago Hi! I've reproduced it successful. I have Microsoft SQL Server 2016 SP2 and I didn't have any errors with it. May be is this problem with UNICODE symbols? At least I don 't have a mistake. The author of this article writes how he bypassed the problem. Could it help you? https://dba.stackexchange.com/questions/240550/sp-whoisactive-get-locks-parameter-xml-error
bbworks
commented
5 years ago Hello, Your URL does not show anything. But, we did get a fix. On Line 4351 of version 11.32, the #locks.resource_description column (due to odd application coding) contains a CHAR(1) "" character. This is from the application executing an sp_getapplock on a resource with that character in the name. Even though the locks column is converted into XML, I do not see anywhere in sp_WhoIsActive that it does the standard REPLACE functions of certain "nonsense" characters for the locks column (or more specifically, for the individual column pieces that go into the XML locks report, including resource_description). When I changed the code to utilize a REPLACE function on the column, the issue seems to be resolved.
l4.resource_description AS [Lock/@resource_description],
->
REPLACE(l4.resource_description, CHAR(1), '') AS [Lock/@resource_description],
Could this fix be incorporated into sp_WhoIsActive? Or perhaps, something more savvy than my hotfix, if applicable?
Thanks,
wdcadmin
commented
5 years ago I'm sorry. You need to copy this URL and to open new browser's window and to insert this URL. The GitHub doesn't open such links. I don't know why. (
amachanic
commented
5 years ago Thanks @bbworks and @wdcadmin for the link and the information. I will incorporate a fix into the proc.
erikdarlingdata
commented
3 years ago I set up a test to figure out which special characters cause XML conversion to barf, and it looks like there are 34 of them.
The column x is the NCHAR(x) code, n is the resulting character. It looks like a nested replace to ditch these would solve the problem, though I don't know how common (m)any of these are in object names. It might be overkill, but 🤷♂️
+-------+---+-------------------------------------------------+---+
| x | n | cmd | b |
+-------+---+-------------------------------------------------+---+
| 1 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 2 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 3 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 4 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 5 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 6 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 7 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 8 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 11 | | SELECT x = CONVERT(xml, '<x>' + ' ' + '</x>' ); | 1 |
| 12 | | SELECT x = CONVERT(xml, '<x>' + ' ' + '</x>' ); | 1 |
| 14 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 15 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 16 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 17 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 18 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 19 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 20 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 21 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 22 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 23 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 24 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 25 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 26 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 27 | | SELECT x = CONVERT(xml, '<x>' + '' + '</x>' ); | 1 |
| 28 | | SELECT x = CONVERT(xml, '<x>' + ' ' + '</x>' ); | 1 |
| 29 | | SELECT x = CONVERT(xml, '<x>' + ' ' + '</x>' ); | 1 |
| 30 | | SELECT x = CONVERT(xml, '<x>' + ' ' + '</x>' ); | 1 |
| 31 | | SELECT x = CONVERT(xml, '<x>' + ' ' + '</x>' ); | 1 |
| 38 | & | SELECT x = CONVERT(xml, '<x>' + '&' + '</x>' ); | 1 |
| 60 | < | SELECT x = CONVERT(xml, '<x>' + '<' + '</x>' ); | 1 |
| 9001 | 〈 | SELECT x = CONVERT(xml, '<x>' + '〈' + '</x>' ); | 1 |
| 12296 | 〈 | SELECT x = CONVERT(xml, '<x>' + '〈' + '</x>' ); | 1 |
| 65286 | ＆ | SELECT x = CONVERT(xml, '<x>' + '＆' + '</x>' ); | 1 |
| 65308 | ＜ | SELECT x = CONVERT(xml, '<x>' + '＜' + '</x>' ); | 1 |
+-------+---+-------------------------------------------------+---+
DETAILS:
ISSUE: Hello Adam. My name is Bradley Biera, a database administrator for the software company Infor. We have been running in a recent issue with sp_WhoIsActive where it does not successfully collect and return results. This is due to:
Error 6841, Severity 16 FOR XML could not serialize the data for node 'Lock/@resource_description' because it contains a character (0x0001) 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.
We have found that by not using the @get_locks=1 parameter, sp_WhoIsActive would then return. The issue must be coming from the locks column. Here is the SQL we are running:
EXEC CODBAProcedures.dbo.sp_WhoIsActive @get_full_inner_text = 1, @get_plans = 2, @get_outer_command = 1, @get_task_info = 2, @get_locks = 1, @get_additional_info = 1, @delta_interval = 10, @destination_table = 'CODBALogs.dbo.monitor_WhoIsActive'
This is indicating that CHAR(1) is being picked up in the locks and the XML is having an issue converting. Is there a way around this? I would strongly assume you already use plenty of REPLACE() functions, as I see them sprinkled through your code. How could we get this fixed? Is it possible you could fix it?
Thank you for your time,