How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.
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'
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try using a stored procedure instead
CREATE PROCEDURE myDynamic
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @table NVARCHAR(100)
SET @table =replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt'
SET @SQL='SELECT * FROM '+@Table
EXEC(@SQL)
CREATE PROCEDURE myDynamic
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @table NVARCHAR(100)
SET @table =replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt'
SET @SQL='SELECT * FROM '+@Table
EXEC(@SQL)
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial