Getting an odd error SQL Server query using WITH clause

Question

The following query   WITH      CteProductLookup ProductId  oid       AS                SELECT p ProductID  p oid         FROM  dbo   ME CatalogProducts  p         SELECT      rel Name as RelationshipName      pl ProductId as FromProductId      pl2 ProductId as ToProductId FROM             dbo   ME CatalogRelationships  rel      INNER JOIN CteProductLookup pl      ON pl oid   rel from oid            INNER JOIN CteProductLookup pl2      ON pl2 oid   rel to oid WHERE     rel Name    BundleItem  AND     pl ProductId    MX12345     Is generating this error      Msg 319  Level 15  State 1  Line 5   Incorrect syntax near the keyword    with   If this statement is a common   table expression  an xmlnamespaces   clause or a change tracking context   clause  the previous statement must be   terminated with a semicolon    On execution only  There are no errors warnings in the sql statement in the managment studio   Any ideas

User · Answer

It should be legal to put a semicolon directly before the WITH keyword

User · Answer

WITH      CteProductLookup ProductId  oid       AS

User · Answer

In some cases this also occurs if you have table hints and you have spaces between WITH clause and your hint  so best to type it like  SELECT Column1 FROM Table1 t1 WITH NOLOCK  INNER JOIN Table2 t2 WITH NOLOCK  ON t1 Column1   t2 Column1  And not  SELECT Column1 FROM Table1 t1 WITH  NOLOCK  INNER JOIN Table2 t2 WITH  NOLOCK  ON t1 Column1   t2 Column1

User · Answer

always use with statement like  WITH then you ll never get this error   The WITH command required a   between it and any previous command  by always using  WITH you ll never have to remember to do this   see WITH common table expression  Transact-SQL   from the section Guidelines for Creating and Using Common Table Expressions      When a CTE is used in a statement that   is part of a batch  the statement   before it must be followed by a   semicolon

[sql-server] Getting an odd error, SQL Server query using `WITH` clause

Examples related to sql-server