Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Solved
Use string as SSRS table name
Posted on 2012-12-28
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'
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
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.
Featured Post
Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers. It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month6 days, 14 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.