Execution Plan StatementText contains incorrect syntax

Posted on

Question :

I have run an execution plan with management studio 2014 on mssql 2008 R2.
To be able to better read the Selectstatement i right-clicked the plan to view it in xml.

I noticed that some statements seem to contain invalid charachters SELECT @IsHeadVersion = IsHead
FROM myTable. At least if i copy them into a new query window i get an invalid syntax error

  <StmtSimple StatementCompId="10" StatementEstRows="1" 
  StatementId="2" StatementOptmLevel="TRIVIAL" 
  StatementSubTreeCost="0.0032831" 
  StatementText="SELECT @IsHeadVersion = IsHead&#xD;&#xA;FROM myTable
   WHERE Id = @VersionID&#xD;&#xA;&#xD;&#xA;
  -- if head  and no delta, select platformIDs and vehicleIDs in this version&#xD;" 
   StatementType="SELECT" QueryHash="0x29CDF8384A77017F" QueryPlanHash="0xF39275675F085D1B">
   <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" 
        ANSI_WARNINGS="true" ARITHABORT="true" 
        CONCAT_NULL_YIELDS_NULL="true" 
        NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

The Statment is dynamically generated inside a stored procedure.

My qustion is could the invalid characters be added by management studio in the xml view?

Or do i have incorrect escape sequences inside the generated sql

Answer :

Community Wiki answer generated from question comments

These are not “invalid characters”. This is a side-effect of formatting the plan as XML; some characters have special meaning in XML and must be encoded.

To read the query text:

  1. Save the XML plan as a *.sqlplan file
  2. Reopen it in SQL Server Management Studio
  3. Right click in the plan window and select “Edit Query Text…”:

    Screenshot

&#xD;&#xA; are carriage return/line feed. You could use SSMS find/replace to either remove them, or set them to start new lines. Just use the regular expression option.
Replacing data using regular expressions

Leave a Reply

Your email address will not be published.