Need your advice on an automatica job

  • Hello,

    I have a project, it's an asp.net application using SQL 2005 as back end (will soon be upgraded to 2008 R2)

    I wonder what would be the best approach to realize this feature:

    My user can receive periodically generated report and the period s/he receives the report will be adjustable by himself/herself.

    My initial thinking is to create a Windows application in .NET and using SQL job to call it. The problem is how do I make it scheduled and the schedule could be updated by user from a front-end page (off course the page can let user to update some field in database)

    Is my idea feasible? If yes, how do I use the front-end page to update the schedule parameter to call the external Windows Application?

    Maybe Reporting Service is better? I never use it before, I used Crystal Report which is such a garb***, headache everywhere. I don't want to use it unless no better solution.

    If there is better approach, please enlighten me.

    Thank you very much.

  • I have used SSRS report subscriptions to send renderings of reports places on a schedule. This would help satisfy the requirement to send the report to your users on a schedule, however I am not sure how easy it is to setup or change these schedules programmatically so the requirement to allow your users to change this schedule to suit may not be simple to handle. I always setup the schedules through Report Manager if the users requested to receive the report at a new time.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • To make things simpler, I feel this would be easier:

    1. Instead of creating a fancy report, just create a stored procedure to generate a spreadsheet and use it as attachment, send it out to recipients.

    2. Create a job to run the sp and schedule the job as wanted.

    The remaining question is: how to programatically change the job schedule?

    Thanks.

  • I guess sp_update_schedule can sort it out.

    Comment and better idea is always welcome. Thanks.

  • halifaxdal (8/30/2012)


    To make things simpler, I feel this would be easier:

    1. Instead of creating a fancy report, just create a stored procedure to generate a spreadsheet and use it as attachment, send it out to recipients.

    That would be fine except you cannot create a spreadsheet using Database Mail. You can create a csv file and name the file with a xls extension but that really not the same thing at all. You will not be afforded any ability to format the display when it opens in Excel and in some newer versions of Excel (2010 at least) a popup will be displayed when the file is opened letting the user know that the file format is inconsistent with what is expected from a file with the xls extension.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • halifaxdal (8/30/2012)


    I guess sp_update_schedule can sort it out.

    Comment and better idea is always welcome. Thanks.

    you said you would like to give scheduling access to users in that case i will use SSRS and ask them to schedule it what ever time they want.

    for jobs we have to give them access to change the schedule, in case they are not authorised to connect to DB directly how will you go for it.

    Regards
    Durai Nagarajan

  • opc.three (8/30/2012)


    halifaxdal (8/30/2012)


    To make things simpler, I feel this would be easier:

    1. Instead of creating a fancy report, just create a stored procedure to generate a spreadsheet and use it as attachment, send it out to recipients.

    That would be fine except you cannot create a spreadsheet using Database Mail. You can create a csv file and name the file with a xls extension but that really not the same thing at all. You will not be afforded any ability to format the display when it opens in Excel and in some newer versions of Excel (2010 at least) a popup will be displayed when the file is opened letting the user know that the file format is inconsistent with what is expected from a file with the xls extension.

    You are right, I meant to creating a csv file, that's what SQL gives us.

  • In SSRS there is feature for automating the reports

    but calling time you can cofigure one time.

    Or

    If you are using Crystal report in .NET interface then user can run report any time.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply