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.

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.

Solution:

Starting with SQL Server 2008 you must always put a semicolon before WITH statement in CTE

But it is not need on below SQL version of SQL 2008 like SQL 2005,SQL 2000

Code:
create table #t
(
  pName Varchar(20),
  DateBegin DateTime,
  DateEnd DateTime
)

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012')


With DateRange(dt) As
(
    Select Convert(Datetime, '01/01/2012')
    UNion All
    Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)

Select T.pName, Dt.dt from #t T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd

Drop Table #t

Correct Code:

Code:
create table #t
(
  pName Varchar(20),
  DateBegin DateTime,
  DateEnd DateTime
)

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player1', '01/04/2012', '01/05/2012')

Insert Into #t(pName, DateBegin, DateEnd)
Values('Player2', '02/01/2012', '02/05/2012')


;With DateRange(dt) As----------------------------------To fix error added semicolon on SQL 2008
(
    Select Convert(Datetime, '01/01/2012')
    UNion All
    Select DateAdd(dd, 1, Dat.dt) From DateRange Dat Where Dat.dt < CONVERT(Datetime, '01/31/2012')
)

Select T.pName, Dt.dt from #t T
Inner Join DateRange Dt on Dt.dt BETWEEN T.DateBegin and T.DateEnd

Drop Table #t