Stack Exchange
SSRS returns Unclosed quotation mark after the character string ''. Incorrect syntax near ''
I'm writing a SSRS report with below dataset definition into a tablix.
DECLARE @SQL AS VARCHAR(8000)

SET @SQL = '
SELECT X,Y,Z 
FROM (
SELECT TABLE_1.XXX AS X, TABLE_1.YYY AS Y, TABLE_1.ZZZ AS Z
FROM TABLE_1
INNER JOIN TABLE_2 ON TABLE_1.ID = TABLE_2.ID
) TEMP'
EXEC(@SQL)

it works fine. However, when I modify the SQL a little bit,it will return  Unclosed quotation mark after the character string ''. Incorrect syntax near ''.
DECLARE @SQL AS VARCHAR(8000)

SET @SQL = '
SELECT X,Y,Z ,POST_CODE
FROM (
SELECT TABLE_1.XXX AS X, TABLE_1.YYY AS Y, TABLE_1.ZZZ AS Z
,CASE 
WHEN POST_CODE IS NOT NULL
THEN SUBSTRING(TABLE_1.POST_CODE, CHARINDEX(CONVERT(NCHAR(1), 0xFDF8), TABLE_1.POST_CODE) + 1, (LEN(TABLE_1.POST_CODE) - CHARINDEX(CONVERT(NCHAR(1), 0xFDF8), TABLE_1.POST_CODE)))
ELSE POST_CODE
END AS POST_CODE
FROM TABLE_1
INNER JOIN TABLE_2 ON TABLE_1.ID = TABLE_2.ID
) TEMP'
EXEC(@SQL)

What I do here is add one more column. The SQL itself can get resultset from SSMS but failed in the SSRS. Does anyone know how to fix the issue? 
Any help would be highly appreciated.

The first example is not valid SQL as the opening = ' is not closed ... it should read TEMP'

Your are missing a closing ' after TEMP.

I wonder it it's choking on that hex value? Try putting it into single quotes - it will NOT be right, but if it runs then at least you know where the problem is! CHARINDEX(CONVERT(NCHAR(1), '0xFDF8')

I have used workaround to solve the issue finally. Separate both datasets and then join them into a tablix. Looks like if the SQL is too long, the SSRS will get some unexpected result.
