Post reply

Starting a job on a linked server

  • August 22, 2007 at 9:17 am

    #180406

    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.

  • August 22, 2007 at 12:09 pm

    #728673

    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.

  • August 22, 2007 at 1:28 pm

    #728702

    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