asked on
Use string as SSRS table name
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'
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'
SSRSMicrosoft SQL Server 2008
Last Comment
BobRosas
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for your help but if I do that I'm not sure how to access the sp. Currently the data I need to access is a text file so my DataSource is an ODBC connection. I don't know how to use my ODBC Data source connection and then connect to the sp. Should I be doing this an entirely different way?
One thing you may want to look at; is made your txt files into XML (layout) file.
since SSRS are just readable XML files.
since SSRS are just readable XML files.
ASKER
Thank you for your input! Is there a place I can go to read more about how to do this? Or can you provide me with more details? I would still need to be able to automatically update the txt file to an xml file and I don't know how to do that.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I've increased points so I can award to both since I'm not sure how I will proceed. Thank you for your help!