Sql-server – Run Multiple Remote Jobs

sql serversql-server-2005sql-server-agent

I need to manually run a job on more than 150 sql server instances (sql server 2000, remote) from a sql server 2005 instance (the local server). The job is the same on all these instances. The job just calls a stored procedure without parameter, which is also the same across all the instances. These jobs are on a schedule. But now they want me to manually run the job for all the instance or for specified instances upon request.

What is the best practice for this? I have tried openrowset to call the remote stored procedure. But each run of the job takes couple of minutes, so if I use a loop to run all these jobs, it will run one by one and that's a long time. Ideally, it should be able to run the stored procedure on each instance without waiting for it to finish. More ideally, it should be able to run the job on each instance without waiting for it to finish, so it can leave a record in the job history on each instance.

And the stored procedure is from a third party so it can't be altered.

update:

since the 'people' ask this to be initialised from a SSRS report, use SSRS to call some T-SQL/proc on the server would be most appropriate. The problem I got now is when calling msdb.dbo.sp_start_job on remote SQL Server 2000 instances from local server using OPENQUERY or OPENROWSET I got Cannot following error.

process the object "exec msdb.dbo.sp_start_job @job_name = 'xxx' ". The OLE DB provider "SQLNCLI" for linked server "xxx" indicates that either the object has no columns or the current user does not have permissions on that object.

I guess this may because the sp_start_job doesn't return anything because I can use OPENQUERY/OPENROWSET to call other remote proc without problem. So any workaround?

update:

I have found it actually pretty simple in t-sql.

EXEC [linkedServerName].msdb.dbo.sp_start_job @job_name = 'test2'

So I don't need to use OPENROWSET/OPENQUERY atually since all the remote SQL Server 2000 instances are already added as remote servers.

Best Answer

I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"

foreach ($SqlServerName in $SqlServerNames) {
    $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

    $SqlServer.JobServer.Jobs[$SqlJobName].Start()
}

All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.

It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.

As you can see, in very few lines of PowerShell code you are able to accomplish this task. But I'd take it a step further, such as error handling. You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlServer.JobServer.Jobs[$SqlJobName].Start()
    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do. Re-work it for your needs/environment.

As per RoKa's comment:

Can this be edited to check for whether the job is currently running, error handling?

Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]

        if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
            $SqlJob.Start()
        }

        else {
            # handle/log accordingly if the job doesn't exist or it's not idle
        }

    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want. Test this out very far from production and be 100% sure it does what you think it will.

EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances. I'm finding this information through this BOL reference.

Related Question