Hello, I am not sure where to start with this as I have never had to run report against a temp table before. I have wrote what I believe is a beautiful query to pull a ton of data into from MANY different tables to accommodate a request from administration. I then wrote a select query to pull in the data they need.
How do I set this up to run every day? I don't believe I can create the temp table right in my query written for the report in visual studio, can I? Do I need to set something up in SSMS for the temp table and then have the VS report run after the temp table is updated every day? Thanks
what reporting tool are you using or are you just dumping it into excel for admin to look at?
1 Like
I am using visual studio 2008 and yes, I would make a subscription to the published report to run daily in excel format and drop to a file location.
and the published report is on SSRS? are you using embedded sql on these SSRS reports or using stored procedure?
1 Like
I have only built the queries and ran them through management studio at this time. I have SSRS setup on the server I am using Visual Studio on and an active database connection to the database I am pulling from. Unfortunately, I have no experience with using stored procedures.
so if you went on a long vacation what happens to the reports that get sent to admin?
easy to create proc
easy to create and deploy ssrs report.
let us know what guidance you need
1 Like
What I am hoping to achieve is....
- each morning around 3am, this temporary table is created and all the data is pulled into (I already wrote the query and have successfully executed it in management studio)
- each morning around 4am, SSRS will automatically run my select query against the temp table created at 3am, and drop it in an excel format to a file drop specified location. I know how to do this part.
What I am looking for guidance on is the first step above; what options do I have for creating that temporary table every morning?
Create a sql job, schedule it to run at 3am every day with the query that you have already created. just follow the steps below
1 Like
Thank you so much! I just created the job for the 3AM schedule I need daily but executed in real time to test and it worked successfully. This was probably a simple thing but with my lack of knowledge and being a rookie, I wasn't aware of my options! You have been tremendously helpful. Thanks again!!
we were once all rookies. glad it worked out.
also not sure if you are aware, you can also make it so that the ssrs report gets the data using stored procedure and schedule the report to email it to admin. that way no 2 step dance. maybe next steps 
1 Like
