I am helping another SQL Server DBA with an issue they have with just a particular SQL Server 2008 R2 instance. I have run this query successfully on my own 2008 R2 instances, 2012 instances, etc. I'll list the query below. The sub-selects are pulling a single specific value from a table-valued function. We have verified that the referenced view column exists, we have verified that the function can be successfully run as a standalone outside of the sub-select, we have verified the customer ID is valid, etc.
DECLARE @CustId varchar(12)
SET @CustId = 'AA100168'
CASE (select WORD from dbo.GETALLWORDS([Customers].[Anniversary],'-') where WORDNUM = 2)
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'Apr' THEN 4
WHEN 'May' THEN 5
WHEN 'Jun' THEN 6
WHEN 'Jul' THEN 7
WHEN 'Aug' THEN 8
WHEN 'Sep' THEN 9
WHEN 'Oct' THEN 10
WHEN 'Nov' THEN 11
WHEN 'Dec' THEN 12
END AS AnnMonth,
(select WORD from dbo.GETALLWORDS([Customers].[Anniversary],'-') where WORDNUM = 1) AS AnnDay,
(select WORD from dbo.GETALLWORDS([Customers].[Anniversary],'-') where WORDNUM = 3) AS AnnYear
FROM dbo.viwUser_Customers_Custom Customers
WHERE [Customers].[Id] = @CustId
Here are the errors that come back for each sub-select.
Msg 102, Level 15, State 1 Incorrect syntax near 'Customers'.
Any suggestions as to what is causing this to fail? It fails to execute in SSMS, but trying to parse the query it comes back clean. I checked that the 2008 R2 deployment is at the same service pack level as other instances where this same query works.