Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
leeholden
Starting Member
34 Posts |
 Posted - 2002-04-22 : 05:20:12
|
| I have 2 SQL 7 Servers (called LIVE and LOAD)LOAD is a loading area where all of my daily processing takes place and only has the latsest dataLIVE is for the users and contains all of the dataThere is a nightly job on LOAD called “LOAD_JOB”I have a job (“LIVE_JOB”) that needs running on ALL of the data so must be executed on LIVE, but it cannot start until “LOAD_JOB” on LOAD has finished.I have tried putting the following step in “LOAD_JOB” EXEC LIVE.MSDB.DBO.SP_START_JOB @JOB_NAME = ‘LIVE_JOB’and I have also tried creating a stored proc on LIVE CREAT PROCEDURE USP_START_JOB AS EXEC MSDB.DBO.SP_START_JOB @JOB_NAME = ‘LIVE_JOB’and then execute this job from the LOAD server EXEC LIVE.STAGING_AREA.DBO.USP_START_JOB (this has linked server permission to execute)In both cases I get an error reporting back saying that “LIVE_JOB” doesn’t existIt seems that it is trying to start a job on the same server as the spawning process, even though I am telling it to go to a different server.I have checked all spelling ect, so if there are any spelling mistakes, it is only in this message.So, does anyone know how I can start a job on LIVE server from the LOAD server? |
|
|
MuadDBA
628 Posts |
Posted - 2002-04-22 : 13:09:46
|
| Well I don't think this is exactly what yoyu're looking for, but it could work nonetheless:On the LOAD server, set up a tabe with a one column: FINISH_FLAG as bit . At the end of your LOAD_JOB, have it set the value to 1. For the LIVE_JOB, have it start at approximately the time the LOAD_JOB is expected to finish, and have it check the temp table on the LOAD server to see if the flag is set to 1. If it is, set it to zero and then execute the LIVE_JOB, if not, use the waitfor command and wait 5 minutes and then look again. Once it is there, reset it, execute the job, and you should be all set.Hope this helps. |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-04-23 : 16:06:03
|
| Another option yoy may consider. OSQL is a dos interface for SQL, through which you can run queries or stored procedures on a remote server. For instance, if you have SQL Mail configured, you could run the following on SERVERA to make SERVERB send you a message:xp_cmdshell 'OSQL -S ServerB -E -Q "xp_sendmail ''YourFriend'' ,@message=''Hello'',@subjet=''Hello''"'Thought I haven't tried it, you should be able to send an sp_start_job command instead. |
|
|
|
|
|
|
|
|
Starting a Job from a remote server