SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need your advice on an automatica job


Need your advice on an automatica job

Author
Message
halifaxdal
halifaxdal
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4408 Visits: 1768
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37202 Visits: 14411
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
halifaxdal
halifaxdal
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4408 Visits: 1768
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.
halifaxdal
halifaxdal
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4408 Visits: 1768
I guess sp_update_schedule can sort it out.

Comment and better idea is always welcome. Thanks.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37202 Visits: 14411
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
durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3309 Visits: 2784
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
halifaxdal
halifaxdal
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4408 Visits: 1768
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.
db_expert_pradeep
db_expert_pradeep
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 548
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search