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 |
|
reenz
Starting Member
29 Posts |
 Posted - 2006-03-29 : 03:31:25
|
| I have a admin settings table that will store a max_row columnsIf my stored proc called by my asp.net page is to return more then the number in max_row, i want to submit it as a batch job to be executed on the ms sql server and output it as an excel file at a user specified location and file name. After which i need to email the user when the excel file is ready for pick up.Can any one point me in the right direction to start? My current idea when count(*) in the stored proc is greated then max_row, i will insert into a batch_job table. Using a scheduled job to run every say 5 mins, read from the batch_job table and then call another stored proc to do the job. However can a stored proc return values to an excel file? Another idea is to use the DTS import/export wizard which will then be able to return as an excel file. However, how do i activate it using a stored proc? There is also the bigger then 65000 rows that excel can support in 1 worksheet to consider as well as performance issue as i have a rather large database warehouse. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-29 : 05:13:53
|
| The easiest way to do it is use DTS, if you create a DTS Package and want to run it from SQL, use DTSRun.. As for the over 65000 records, you will need to test for this and either output accordingly or send a message to the user asking them to refine their report.. |
 |
|
|
reenz
Starting Member
29 Posts |
Posted - 2006-03-29 : 20:33:40
|
| But how do i get it to export to excel? from what i know and tried when i used dts packages, it needs me to specify the location of the excel file with a fixed. However, as my web application caters to multiple users who can dynamically construct the sql query, it is almost impossible for me to create an excel file with the right format beforehand at a predefined area. i need to save the excel file with a filename specified by the users. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-30 : 04:25:48
|
| Use bcp then, you can then pass in a path and save it comma seperated.. |
|
|
|
|
|
|
Batch Job