Tuesday, March 24, 2015

Error Unable to show XML. The following error happened. There is an unclosed literal string. Line 1, position 2097154.

Recently when I was working on optimization of a store procedure. I tried to see the execution plan of that procedure using DMV sys.dm_exec_query_plan by passing the Plan_handle of that store procedure. When I clicked on the query_plan hyperlink to display the graphical execution plan I got below error window saying that:

"Unable to show XML The following error happened. 
There is an unclosed literal string. Line 1, position 2097154."

I have faced this problem earlier on my system so I was aware that how to fix it. Actually this error comes in picture because of limitation set by default of 2 MB for XML data.  The XML Data for my procedure was more than 8 MB. We can check the size of our cached plan by using DMV sys.dm_exec_cached_plans. So, To see the graphical execution plan we have to increase the size of XML Data. We can follow  below steps to increase the XML data size:

From SSMS Go to Tools > Options >Query Results> SQL Server > Results to Grid 

Here we can see that only 2 MB data size is specified for XML data. Just increase it to 5 MB or unlimited according to your situation. Click OK.

Go back and execute the query. BINGO!!!!! Problem Solved. :)


No comments:

Post a Comment