I have a data source that is a folder filled with daily txt files. Everyday I want to be able to automatically read yesterday's file. In Report Services if I use the following code I am able to see the contents of a file.
SELECT *
FROM 12272012.txt
In SSMS if I use this code...
SELECT replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt' as ErrLog
It produces the file name I need (Ex: 12272012.txt) but it doesn't work in the FROM statement of SSRS. Please help me with how I need to change this to make it work in SSRS?
SELECT *
FROM replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt'