Sql-server – Powershell script running for SQL Server 2008R2 but not for higher version

powershellsql server

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'xxx@xxx.com',
   @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='xxx@xxx.com',
  @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
 }`

Best Answer

I first installed SSMS 2014 on the same machine having SQL Server 2008R2.

Then I installed the Powershell Provider for SQL Server 2014.

After that I am able to fetch the details from the higher versions of SQL Server like 2012 as well.

There is no need to update Powershell.