Starting a job on a linked server
-
I would like to start a job on a linked server as the final step of a job. So I can run
DECLARE
@rc int
EXECUTE @rc = <linked server>.msdb.dbo.sp_start_job <jobname>
and it startes the job but I get an error
Job <jobname> started successfully.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Is there anyway to eliminate this error?
Both servers are running SQL Server 2000 SP3a.
-
As I recall you cannot kick of by using the the linked server name but instead you have to use the Server Name parameter of sp_start_job, see BOL. If you do as you have it still starts causes on the local server and not the linked server.
-
At my last employer we logged ftp transfers to a SQL Server database table on one server (using virtual directories, the files actually we loaded to a second server) to start data import processes once a download was successfully completed. The execute statement from one of the triggers looks like this:
EXECUTE @intReturnStatus = CRAPGAME.msdb.dbo.sp_start_job @job_name = @vchJobName
There are many triggers on the first server that is used to start imports on the second. Each trigger initializes the variable @vchJobName with the jobname that needs to be started. This let us use cut and paste as data transfers were added to the server.
Based on what I see, you are doing it right, I just can't explain the error message you are getting.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply