Best way to run multiple SPs via an ASP script

Question

Best way to run multiple SPs via an ASP script

paul-941374

Say Hey Kid

Points: 701
More actions
January 28, 2010 at 12:34 pm

#215132

I'm moving from a dedicated server with SQL Server 2005 to shared hosting running SQL Server 2008. I have a daily SQL Job on the SQL Server 2005 which executes about 15 SPs and takes about 3 minutes to run. I don't expect I will be able to run SQL jobs in the shared SQL Server 2008 so I'm trying establish the best options available.
As far as I can tell the options are:
1. Connect via a local copy of Microsoft SQL Server 2008 Express but I don't know if this version will give me SQL JOBS.
2. Create an ASP script to execute all 15 SPs via a CRON. Not sure what challenges and limitations this may have.
Could someone please advise me on the best solution?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

paul of all the shared hosting i help friends with or have for myself, none give me the ability to run jobs; most do not even let me run backups.

i'm sure you'll end up doing some sort of chron/scheduled task to run your procs.

what are your procs doing on a daily basis? db maintenance? data migration? data updates? if it's db maintenance, some of them might not be allowed by your shared host;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

paul-941374 Say Hey Kid Points: 701 More actions · Answer 2

Hi. The SPs import XML data, format it and insert it. Is it best to use run a job using a local copy of SQL Server 2008 Express that connects to the remote DB?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

SQL Express does not include SQL Server Agent so you can't run jobs from it.

I'd suggest creating a batch file that uses sqlcmd to execute the procedures. You could then schedule the batch file to run using the windows scheduler.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

paul-941374 Say Hey Kid Points: 701 More actions · Answer 4

Thanks Jack I didn't know about that option, but it looks like it might do the job. Do I have any issues with timing out if I execute about 15 sprocs in one sqlcmd?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I don't know if you'll have timeout issues. Mainly because I've never done it. I just know it can be done. I'd think you'd be all right because you'd have to batch the executions any way.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question