8 Replies Latest reply on Mar 10, 2017 2:48 PM by m60freeman

    Display of SQL text (SQL Server), possible bug

    m60freeman

      When displaying the SQL text for a hash, it displays the statement, followed by the entire stored procedure text (if the statement is part of a procedure. However I'm having a problem with the display of SQL text that is not part of a procedure.

       

      We have written custom code for Entity Framework that injects a comment containing the location of the method that contains the LINQ from which the SQL is being generated into the generated SQL itself. This is really helpful, except when the generated SQL is very long.

       

      For a relatively short statement, I'll see something like this:

      -- Core.Repositories.PurchaseOrderHistoryRepo.GetPurchaseOrderLineItems

       

      SELECT [Extent1].[SKUId] AS [SKUId], [Extent1]

      .[doh] AS [doh], [Project1]

      ...

      (DPA shows the entire thing, I just put in the ellipsis to conserve space.)

       

      However, if it is very long, I see something like this:

      /* (inserted by DPA)

      Text truncated due to excessive length

      */

      SELECT [Project1].[id] AS [id], [Project1]

      .[doh] AS [doh], [Project1]

      ...

       

      But if I hunt it down in sys.dm_exec_sql_text.test, I see this:

      (@p__linq__0 decimal(5,0))-- Core.Repositories.InventoryRepository.GetAllNonDiscontinuedBySupplierId

      SELECT

          [Project1].[id] AS [id],

          [Project1].[doh] AS [doh],

      ...

      Note that the first line is not shown by DPA. That first line (shown in red for emphasis) not only shows me what the parameter is, it also includes our injected comment. DPA must be filtering out everything before the SELECT keyword. This is an anti-feature.

      Also note that in both examples, the SQL is getting reformatted (it is wrapped differently than what is shown by the DMV) and in the first example a blank line is inserted between the comment and the SELECT whereas in the second example the line before the select isn't separated by a blank line, it is instead completely removed. (I tried to find a short example that also had a parameter but was unable to find one quickly.)

       

      I haven't investigated enough to determine if the text is being stored in the repository the same way it is coming out of the DMV, in which case this is a UI issue, or whether it is an issue with the SQL being executed to populate the repository.

       

      I'd submit a feature request to improve this, but I think it should be considered a bug to be fixed and made a higher priority.

       

      While I'm here, consider this a plug for a feature request I submitted that is tangentially related: Allow values larger than 7999 for SQLTEXT_SQLSERVER_MAX_TEXT_LENGTH