0

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
 }`
9
  • There are differences in the powershell provider between 2008R2 and 2012. We had to include different ones depending on which we were connecting to. Sorry, no longer have access to source for details. Sep 5, 2016 at 21:09
  • What version of Management Studio is installed on the SQL 2008 R2 machine? Try running the script from a machine that has SQL 2012 or better management studio installed and see if the problem persists. Sep 6, 2016 at 2:57
  • @MichaelGreen Thanks for the response. So you wanted to say that i need to install a SQL Server 2012 Powershell Provider on SQL Server 2008R2 machine, if I want to make this script work for 2012??? Or something else needs to be done.... Sep 6, 2016 at 5:30
  • @JonathanFite Thanks for the response. I will check the SSMS Version and let you know. Also I will check if the script works in 2012 machine as intended.... Sep 6, 2016 at 5:32
  • Yes, I seem to remember that's what we ended up doing. Sep 6, 2016 at 8:34

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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