Run SQL Server SPs via ASP script with email reporting

benners70
benners70 used Ask the Experts™
on
I currently have a daily SQL JOB on a dedicated server which runs about 15 SPs. I'm looking to move this SQL DB to shared hosting and I don't expect I will have access to SQL JOBS so I need to find another way to run these SPs daily via a script. I need the script to send an email report so I know if any SPs failed.

The SQL JOB runs for about 3 minutes so I need to make sure the script doesn't timeout.

Can someone advise how this may be done in ASP.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SharathData Engineer

Commented:
You have to do this thru JOB only. To my knowledge there is no other way.

Author

Commented:
Maybe my question isn't very clear.

I'm not experienced with this, but I know you can call SPs via ASP and you can pass in parameters and accept returned values (http://support.microsoft.com/kb/164485). I'm after a bit more clarification on how to go about running 15 SPs from 1 ASP script and emailing any errors.

If this isn't a good option I may be able to connect via a local SQL Server to the Share SQL Server and run the SPs via a job on my local SQL Server. I think it would be better to keep all the site functions in one location though hence the ASP question.

You can call Stored Procedures with this syntax:  EXEC <sp_name>  http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

WIth "on error resume next," you can trap errors and collect error details.  http://www.aspdev.org/articles/asp-error-handling/

With CDOSYS, you can generate and send email messages with the error details.  http://www.w3schools.com/asp/asp_send_email.asp

However, if this is a public facing app, I'd suggest you store the error details on the server (in a database, perhaps), rather than sending the details via email (not a secure protocol).  You could send email alerts that only advise of new errors, and then your administrator could follow a hyperlink to log into an interface to view the errors (which are stored in a database, or XML, etc.).  

Author

Commented:
Thank you for the various suggestions. Another solution I have found which I'm going to investigate further is the use of sqlcmd http://msdn.microsoft.com/en-us/library/ms162773.aspx 

Author

Commented:
Suggesting SQLCMD would have made the solution more complete.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial