Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2006-02-21 : 09:37:12
|
| Howdy allIs there any way to automate creating an excel spreadsheet and saving the results in SQL Server? I have created a template in Excel that connects to the database in question, and runs a couple of views. This refreshes each time someone opens the spreadsheet. however, it also relies on someone opening the spreadsheet. Is there any way that I can create a DTS (for example), that runs a view, imports into an excel template, and saves the results as a workbook in a specific location? Then i can schedule this to run every month? Or any other method similar? Thanks in advanceHearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-02-21 : 10:05:19
|
| Oh, and I need to send the excel spreadsheet in an email (using sp or xp_sendmail??)Hearty head pats |
 |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2006-02-22 : 15:23:18
|
| Wouldn't SQL Server Reporting Services be a better solution? You could have a subscription execute the report and send it periodically to all of the users that need it. The nice part is that if you already have the views/procs defined to retrieve the data then you only need to create the report. You should really check it out.JamesH |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-02-22 : 19:33:17
|
| create a job that will run as per schedulethe job contains the sql proc or tsql to bcp out the result to excelnext step in job would be to call xp_sendmail to send a mail with attachment (xls file)for details, you may need to consult BOLHTH--------------------keeping it simple... |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-02-23 : 10:43:10
|
Thankyou both for your suggestions. We do have reporting services, however, we don't currently have it installed (why not I do not know). But I shall push to get it sorted, adn then I can try out both suggestions.Thanks again  Hearty head pats |
|
|
|
|
|
|
Automating Report Generation