Solved

Use string as SSRS table name

Posted on 2012-12-28
6
392 Views
Last Modified: 2013-01-02
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'
0
Comment
Question by:BobRosas
  • 3
  • 2
6 Comments
 
LVL 8

Assisted Solution

by:venk_r
venk_r earned 150 total points
ID: 38729183
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)
0
 

Author Comment

by:BobRosas
ID: 38733423
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?
0
 
LVL 27

Expert Comment

by:planocz
ID: 38734594
One thing you may want to look at; is made your txt files into XML (layout) file.
since SSRS are just readable XML files.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:BobRosas
ID: 38737875
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.
0
 
LVL 27

Accepted Solution

by:
planocz earned 150 total points
ID: 38737940
you  will need to most likely do it in VB or C# coding. Their is a standard import file from the framework that has all the info to make and read a XML file. It  would be too much detail work to post here. you may want to get a code book or look online to "How to create and read XML files".
0
 

Author Comment

by:BobRosas
ID: 38738465
I've increased points so I can award to both since I'm not sure how I will proceed.  Thank you for your help!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now