First one or two lines missing from SQL statement when using SSMS

Question

I have been using SSMS for about three years now. I have some quite complex SQL View statements, and many of them have a problem when using SSMS to edit in "Design" mode, or when trying to use "Create To" functions. If I have any statements before the initial SELECT, it likes to omit those. Thus, the resulting SQL is incomplete. However, the Views work perfectly.

The problem is that if I leave these go, I forget what the missing parts of the statements are. As in the case now. I waste so much time trying to recreate what was missing. Its quite rediculous. This has been happening in SSMS 17 and now 18. Presently in 18.8

I posted this in a forum not long ago, and it was like I was a lunatic or something. Surely someone else has seen this problem, and knows whats going on and why. Its been around far too long for it to be only be noticed by me. I'm quite an amateur with this. But, I've had some tremendous help by experts in these forums!!!

Now, for my example. This is a perfectly good and running SQL View. Its was created in SSMS 17 about 2 years ago.

with cte as

(SELECT *
FROM   dbo.VariableValue
WHERE (2 = CASE WHEN (VariableID = 73 OR
           VariableID = 74 OR
           VariableID = 75 OR
           VariableID = 76) AND ConfigurationID = 2 THEN 2 WHEN NOT (VariableID = 73 OR
           VariableID = 74 OR
           VariableID = 75 OR
           VariableID = 76) THEN 2 END)), ALL_COMBINED AS
    (SELECT TOP (100) PERCENT DocumentID, [49] AS [DwgRevision], [47] AS [Description], [45] AS [ProjectName], [54] AS [PartNumber], CurrentStatusID, Pending, LatestRevisionNo, Filename, [57] AS [Version], [56] AS [Material], [73] AS [RvTbl_Description], [76] AS [RvTbl_DwgDate], [75] AS [RvTbl_Approved], [74] AS [RvTbl_Revision], 
              TransitionID
   FROM   (SELECT DocumentID, VariableID, TextOutput, CurrentStatusID, Filename, TransitionID, LatestRevisionNo, CASE WHEN CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending
               FROM   (SELECT cte.DocumentID, cte.VariableID, Documents.CurrentStatusID, dbo.TransitionHistory.TransitionID, Documents.Filename, Documents.LatestRevisionNo, cte.ConfigurationID, cte.RevisionNo, cte.ValueText AS TextOutput, ROW_NUMBER() OVER (PARTITION BY cte.DocumentID, cte.VariableID
                           ORDER BY cte.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
               FROM   dbo.Documents INNER JOIN
                          cte ON dbo.Documents.DocumentID = cte.DocumentID INNER JOIN
                          dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
               WHERE (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND cte.VariableID IN (57, 56, 54, 49, 47, 45, 73, 76, 75, 74)) t
   WHERE Seq = 1) doc PIVOT (Max(TextOutput) FOR VariableID IN ([57], [56], [54], [49], [47], [45], [73], [76], [75], [74])) AS pvt
WHERE [54] <> 'NULL')
    SELECT ALL_COMBINED.*, dbo.DocumentsInProjects.ProjectID
  FROM   dbo.DocumentsInProjects INNER JOIN
              ALL_COMBINED ON dbo.DocumentsInProjects.DocumentID = ALL_COMBINED.DocumentID

When I try to edit this code, I get this in designer (truncated for clarity):

SELECT *
FROM   dbo.VariableValue
WHERE (2 = CASE WHEN (VariableID = 73 OR
           VariableID = 74 OR
           VariableID = 75 OR
           VariableID = 76) AND ConfigurationID = 2 THEN 2 WHEN NOT (VariableID = 73 OR
           VariableID = 74 OR

...

So, where did the "with CTE as (" go? Like I said. I have more than one statement like this. SSMS does not like anything prior to the initial SELECT statement, and wiped it out.

Answer 1

Just to clarify, this post is not a duplicate of

https://learn.microsoft.com/en-us/answers/questions/751901/missing-initial-statement-from-sql-view-in-design.html

This post is an issue with how to use designer properly, or why my code disappears.

The other post is a question on alternate ways to extract missing code. Two different questions. I have found making concise single issue questions are the quickest and easiest way to get an answer.

Thank you.

Answer 2

I was not able to reproduce this. I created this nonsensical view:

CREATE VIEW MyView AS
   WITH CTE AS 

   (SELECT o.name AS object_name, COUNT(*) AS colcnt
    FROM   sys.objects o
    JOIN   sys.columns c ON o.object_id = c.object_id
    GROUP  BY o.name
   )
   SELECT object_name, colcnt
   FROM   CTE
   WHERE  colcnt % 3 = 0

When I clicked Design from the context menu or selected Script as, the line WITH CTE AS was there. I'm running SSMS 18.11.

What do you see if you run

EXEC sp_helptext 'YourView'

Anyway, I guess you can add the missing WITH CTE line once you have scripted the view.