I am running below Powershell script from a SQL Server 2008R2 machine. The purpose of the script is to create SQL Job on all the mentioned SQL Server instances remotely in one go. The servername,instancename and PortNumber has been mentioned through the notepad. It is able to do so on a 2008R2 instance but not on SQL Server 2012. I always have a doubt that whether powershell scripts depends on the SQL Server version or not?
It will be great if somebody can help me clearing my doubt and resolving the issue.
Error Message:
The below error message keeps on repeating for all the parameters that we are setting for a job like OwnerLoginName, Subsystem,Command etc etc
New-Object : Exception calling ".ctor" with "2" argument(s): "SetParent failed for Job 'SQL Services Restarted Aler " At E:\Sachin\create-SQLJOB_AllInstances_V1.ps1:18 char:16 + $j = new-object <<<< ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($s.JobServer,'SQL Services Restarted Aler + CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand
Property 'Description' cannot be found on this object; make sure it exists and is settable. At E:\Sachin\create-SQLJOB_AllInstances_V1.ps1:19 char:4 + $j. <<<< Description = 'Alert to identify the SQL Services Restart' + CategoryInfo : InvalidOperation: (Description:String) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound
You cannot call a method on a null-valued expression. At E:\Sachin\create-SQLJOB_AllInstances_V1.ps1:21 char:10 + $j.Create <<<< () + CategoryInfo : InvalidOperation: (Create:String) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull
Thanks in advance.`
function CreateSQLJob{
param (
[string]$svr,
[string]$inst,
[string]$port
)
foreach ($instancename in $inst)
{
$ConnectionString = "data source = $inst,$port; initial catalog = master; trusted_connection = true;"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server')
$s.ConnectionContext.ConnectionString = $ConnectionString
$j = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') ($s.JobServer,'SQL Services Restarted Alert')
$j.Description = 'Alert to identify the SQL Services Restart'
$j.OwnerLoginName = 'sa'
$j.Create()
$js = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($j,'Step 01')
$js.SubSystem = 'TransactSql'
$js.Command = "IF(SELECT DATEDIFF(MI,Crdate,GETDATE()) FROM master.dbo.sysdatabases WHERE NAME='TEMPDB')<=2
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name='XXX',
@recipients=N'[email protected]',
@subject=N'SQL Services Restarted on on Server - $inst',
@body=N'This is an informational message only: SQL services possibly restarted on this server. Please restart any dependent application services after verifying status with DBA Team first.'
END"
$js.OnSuccessAction = 'QuitWithSuccess'
$js.OnFailAction = 'GoToStep'
$js.OnFailStep=2
$js.Create()
$SQLJobStep2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $j,"Failure Notification"
$SQLJobStep2.Command = "EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXX',
@recipients='[email protected]',
@subject = 'SQL Services Restarted Alert job - FAILED on $inst',
@body = 'Hi Team,<br><br>
SQL Services Restarted Alert job failed . <br><br>
Thank You.',
@body_format = 'HTML' ;"
$SQLJobStep2.DatabaseName = "master"
$SQLJobStep2.OnSuccessAction = "QuitWithFailure"
$SQLJobStep2.OnFailAction = "QuitWithFailure"
$SQLJobStep2.Create()
$jsid = $js.ID
$j.ApplyToTargetServer($inst)
$j.StartStepID = $jsid
$j.Alter()
$jsch = new-object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($j,'Sched 01')
$jsch.FrequencyTypes = 'AutoStart'
$jsch.ActiveStartDate = get-date
$jsch.Create()
write-host "SQL Services Restarted Alert Job created on server $inst"
}
}
$servers = Get-Content 'E:\Sachin\SQL_Servers1.txt'
foreach ($sv in $servers) {
$srvr = $sv.Split(",")
$server = $srvr[0]
$instance = $srvr[1]
$port = $srvr[2]
CreateSQLJob $server $instance $port
}`
