Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need your advice on an automatica job Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 7:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 AM
Points: 806, Visits: 1,440
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.
Post #1350379
Posted Monday, August 27, 2012 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 7,098, Visits: 12,604
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
Post #1350625
Posted Thursday, August 30, 2012 7:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 AM
Points: 806, Visits: 1,440
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.
Post #1352210
Posted Thursday, August 30, 2012 7:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 AM
Points: 806, Visits: 1,440
I guess sp_update_schedule can sort it out.

Comment and better idea is always welcome. Thanks.
Post #1352214
Posted Thursday, August 30, 2012 8:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 7,098, Visits: 12,604
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
Post #1352248
Posted Thursday, August 30, 2012 8:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1352259
Posted Friday, August 31, 2012 7:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 AM
Points: 806, Visits: 1,440
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.
Post #1352809
Posted Monday, September 3, 2012 5:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:55 AM
Points: 18, Visits: 444
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.
Post #1353453
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse