Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

5 Answers

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.

share|improve this answer
    
Can this be edited to check for whether the job is currently running, error handling? –  RoKa Mar 26 '13 at 16:49
    
@RoKa Great point, please see my edit. –  Thomas Stringer Mar 26 '13 at 17:00
    
Great! You got my upvote! –  RoKa Mar 26 '13 at 17:02
    
@ThomasStringer Thanks. I am not familiar with powerShell though. Actually they (supporting people) want this to be parametrized. The original requirement is to open a SSRS report then select some instances (parameter), then run. There will be another SSRS report to check job history table so that they know the job result. So can PowerShell can do this (parametrization?). The SSRS requirement can be changed, they just need some way to do it. On the other hand I am also looking at the T-SQL approach but am always willing to learn Powershell and this is a good opportunity for me if it can work. –  thotwielder Mar 26 '13 at 17:26
add comment

If you didn't like the PowerShell option you could use a loop in T-SQL and openrowset to call msdb.dbo.sp_startjob and just start the job.

share|improve this answer
    
Yes, that's something I tried initially but got some problems. I will update when I work it out. Thanks. –  thotwielder Mar 27 '13 at 9:33
add comment

Would an Agent Master Job work for you? Have a look at this:

http://msdn.microsoft.com/en-us/library/ms190662.aspx

share|improve this answer
add comment

You can generate a script file and run this from SSMS in SQLCMD mode. Since you are just kicking off an asynchronous job the command should run quickly. Pretty much the same answer as @mrdenny though. :)

:connect server1
exec msdb..sp_start_job @job_name = 'your_job'
go
:connect server2
exec msdb..sp_start_job @job_name = 'your_job'
go
:connect server3
exec msdb..sp_start_job @job_name = 'your_job'
go
:connect server4
exec msdb..sp_start_job @job_name = 'your_job'
go
/*
add more servers here
*/
:connect serverN
exec msdb..sp_start_job @job_name = 'your_job'
go
share|improve this answer
    
Yeah, that's one approach. The problem is they want to initial it from a SSRS report. So I will try to work it using T-SQL. I will update when I work it out. Thanks. –  thotwielder Mar 27 '13 at 9:35
    
Gotcha, user's needs are what they are. :) –  Jason Cumberland Mar 27 '13 at 13:25
add comment

I have done this by using Central Management. Just right click on the list of servers and run one query on all of them at the same time

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.