January 19, 2007 at 8:31 am
Guys,
I am using SQL Server 2000.
I have to perform the following task daily (which I would like to automate, if possible):
1. Run a stored procedure
2. Copy and paste the results of the stored procedure into an excel spreadsheet
3. Send out the spreadsheet via email
If it weren't for the excel spreadsheet, I'd just create a job to do it and schedule it as necessary. The excel spreadsheet is what complicates the situation.
I have been exploring the usage of DTS to solve this problem. However, my initial investigation (and I could be wrong) is that DTS would not work or it would not work alone (i.e. I would need to invoke DTS using some other language). I am looking for a pure database solution with no outside intervention.
Is that possible?
Any suggestion is greatly appreciated!
Thank you
January 19, 2007 at 9:08 am
Create your DTS package, then right-click on it and choose Schedule Package. This will create a SQL Server Agent job that invokes the package using the dtsrun command line utility. You may want to read about dtsrun in Books Online so that you understand exactly what it does and what your different options are
John
January 19, 2007 at 9:43 am
Hey John,
Thanks for the tip. I am now 1 more step closer to finding an answer to the question, as I can now schedule the DTS package once it is created properly.
I was actually able to create a simple DTS package which runs the sp and sends result to the excel spreadsheet.
One thing I don't like is that I need to provide excel spreadsheet as an input (i.e. I can't create it on a fly with any name - as I want the name to change daily)
Is there a way around it?
Thanks
January 20, 2007 at 7:58 am
i hope u r running the stopred procedure as a job. u can output the results of the stored procedure to the excel file in the step properties of the jobitself after that send that file thru mail as u need that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
January 22, 2007 at 1:35 am
Have a read about dynamic properties in DTS. It's not something I've used a lot myself, but I think it will do what you need it to.
John
January 22, 2007 at 8:40 am
Sugesh: Thanks for the suggestion. That definitely works. However, it looks like the whole output goes into the 1st column of the excel spreadsheet, whereas I want each column of the output table to be in the corresponding column of the excel spreadsheet
John: Let me read about dynamic properties in DTS.
Thanks a lot guys!
January 29, 2007 at 6:26 am
Sure i shall check for your requirement i hope there is a easy solution that can be done.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply