| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2002-12-31 : 22:43:11
|
| I have a major problem, I run an online RPG which is automated using ASP, T-SQL, and SQL Server 2000. I just switched hosts and my new host does not let you use the SQL Server Agent. I was using the agent to run my nightly update scripts at 10:00 PM each night. Well now that I can’t use the agent I need a new solution. The solution must be able to run it’s self at the same time each night with out me telling it to. Anyone have any ideas? Thanks for the help!--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-01-01 : 01:53:19
|
| HiDo you have a machine at home that is always running ?If so, set up your ISP's server as a linked server to your home one. Then run your local scheduler to run the scripts on the linked server.Damian |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-01 : 06:34:43
|
| Can you use the NT Scheduler ? If so you can setup a task to run a bat file containing osql commands to run your script(s).HTHJasper Smith |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 12:37:27
|
| @MerkinI don't have a machine that I could use at home.@jasper_smithI don't know how to make .bat files.--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-01 : 12:42:03
|
| Ask your host to set up the job for you, or get another host. It's a pretty simple request, and if they're willing to lose your business rather than help you with it, they're probably the kind of host you don't want to deal with anyway. |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 13:04:57
|
| @robvolkWould I still need to creat the T-SQL files my self? Or would that .bat file have all the code in it?--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-01 : 13:59:47
|
Eagle_f90,A .bat file is simply a file that contains command lines.For example to obtain a directory listing you would type in the command line.dirTo switch directories you would type cd c:\directoryIwantTo launch a program you would c:\directoryIwant\programtolauch.exe now if you wanted to run a bunch of commands in batch you would create a file with the commands in order and name it with an extension of .batex:abc.bat might look likecd c:\directoryIwantprogramtolauch.exe If you then run abc.bat it would launch your program.Edited by - ValterBorges on 01/01/2003 14:01:41<edit> to fix display </edit>Edited by - robvolk on 01/01/2003 22:14:54 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-01 : 14:05:06
|
| The best way to do it is to put the code into a stored procedure (assuming you can create stored procedures on your server...if you can't, dump that host TODAY and find another one)Once the sproc is created, then you can create a simple job with a single step that executes that procedure. They should have no problem setting up a job like that. It's about 5 mouse clicks with Enterprise Manager. They'd need to create a T-SQL step that includes the text "EXECUTE myStoredProcedure", and then set the schedule for it. The really nice thing about making it into a stored procedure is that you can modify the procedure to do more (or less) without needing to change the job or its schedule.If they don't want to (or can't) create the job using Enterprise Manager, look in Books Online for sp_add_job, sp_add_job_step, etc. and write out the commands needed to create the job using T-SQL. Then you can send them that script and have them run it from Query Analyzer with the proper permissions.Need some clarification: when you say "they don't allow SQL Agent" does that mean they aren't running it (and never will)? Or that you just don't have permissions to create jobs? If they will never have SQL Agent running, then you'd have to use another of the methods everyone else described, or get another host. |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 14:06:15
|
| How would I use a .bat file to run a T-SQL script?--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For meEdited by - Eagle_f90 on 01/01/2003 14:18:17 |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 14:19:08
|
| @robvolkTHis is what they say about the SQL server agent:SQL Server agent is not running on any of our database servers. According to our Admin group it is not configured for shared hosting use.--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-01 : 22:05:41
|
| take a look at the osql utility in BOL. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-01-01 : 22:11:57
|
And find a new host. Then tell your old one why they have lost your business.Edit : ummmm yeah, what Rob said  DamianEdited by - merkin on 01/01/2003 22:16:53 |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 22:23:59
|
| Is it possible to configure the SQL server agent for shard hosting use?--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-01 : 22:42:21
|
| I think what they mean is that they can't differentiate which account runs a job under SQL Agent, in order to bill that account appropriately. I don't know if it can be done or not, and I can envision a few scenarios where SQL Agent can be abused pretty badly in a shared hosting environment and understand why they wouldn't necessarily want to provide it. But the fact remains, there are hosts out there that can provide the service for you; why pay for a host that doesn't? |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 22:49:25
|
| DO you know any cheap host that allows the use of it?--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-01 : 22:49:32
|
| Rob,What host would you recommend? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 23:09:49
|
| I just had an idea, I have a licens for SQL 200 Server. Would it be possible for me to install SQL on my computer and setup the SQL Server Agent jobs on my system to mode a database on anohter system?--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-01 : 23:15:46
|
| Yes. That's what Damian was describing in his first reply. |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2003-01-01 : 23:19:01
|
| Must have missed that one, how hard would it be to setup the link on my side? I know almost nothing about administrating SQL--For those with wings, fly to your dreamsIt is all or nothing there is no in-betweenRemember who I was... For me |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-01 : 23:25:28
|
| Look up "linked servers" and also "remote servers" in Books Online, and read everything you can on them. You can probably do it using remote servers, but linked servers might be more flexible.Frankly, if it's too daunting to set up yourself, just get another host and let them set it up for you. You risk invoking your current host's wrath if you mess something up, even if it's relatively minor (and then you'll be looking for a new host anyway) |
|
|
|
Next Page
|
Running Scripts
Ahhhh, not cheap however, but you get what you pay for, and their support alone is worth it.Assuming you're not on Brinkster already, they have a package that supports both web and SQL Server hosting: