none
PowerShell - Create operate Best Practice

    Question

  • I can't seem to get this right.  I'm very new to PowerShell.  I just need to make sure I'm headed in the right direction with this script.  What's wrong with it?

    # Import Server names


    $server

    =Get-Content-pathc:\users\hawkc\testservers.txt



    # Load SQL Server Assembly


    [

    System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.SMO.Server') |Out-Null



    # Load JobServer Assembly


    $jobserverNamespace

    ="Microsoft.SqlServer.Management.Smo.Agent"


     


    ForEach

    ($servernamein$server)

    {



    # Create a connection to the server


    $server

    =new-object"Microsoft.SqlServer.Management.Smo.Server"$servername

    $username

    ='xxxxxxxx'


    $password

    ='xxxxxxxxx'


    $sqlConnection

    =New-ObjectSystem.Data.SqlClient.SqlConnection


    $sqlConnection

    .ConnectionString ="Server=$servername;Data Source=$server;Database=msdb;User=$username;Password=$password"



    $sqlConnection

    .Open()



    $op

    .Create=new-object($servername.$jobservernamespace.jobserver.Operator)


    $command

    =@{name='DBA Group'; email="'connie@anycompany.com';'crystal@anycompany.com';'waliyu@anycompany.com'"}

     

     


    $sqlConnection

    .close()

    }

    Any suggestions are appreciated.

    Tuesday, February 06, 2018 6:43 PM

All replies

  • I can't seem to get this right.  I'm very new to PowerShell.  I just need to make sure I'm headed in the right direction with this script.  What's wrong with it?

    # Import Server names


    $server

    =Get-Content-pathc:\users\hawkc\testservers.txt



    # Load SQL Server Assembly


    [

    System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.SMO.Server') |Out-Null



    # Load JobServer Assembly


    $jobserverNamespace

    ="Microsoft.SqlServer.Management.Smo.Agent"


     


    ForEach

    ($servernamein$server)

    {



    # Create a connection to the server


    $server

    =new-object"Microsoft.SqlServer.Management.Smo.Server"$servername

    $username

    ='xxxxxxxx'


    $password

    ='xxxxxxxxx'


    $sqlConnection

    =New-ObjectSystem.Data.SqlClient.SqlConnection


    $sqlConnection

    .ConnectionString ="Server=$servername;Data Source=$server;Database=msdb;User=$username;Password=$password"



    $sqlConnection

    .Open()



    $op

    .Create=new-object($servername.$jobservernamespace.jobserver.Operator)


    $command

    =@{name='DBA Group'; email="'connie@anycompany.com';'crystal@anycompany.com';'waliyu@anycompany.com'"}

     

     


    $sqlConnection

    .close()

    }

    Any suggestions are appreciated.

    Tuesday, February 06, 2018 6:55 PM
  • Hi Studyhawk2,

    Based on the above code, it looks like that you are going to create operators in SQL Server Alert using Powershell script, please refer to the following code, I have tested it and it works.

    Foreach ($server in Get-Content "c:\users\hawkc\testservers.txt")  # Import Server names
    
    {
    
        # Create a connection to the server
        
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null  # Load SMO Assembly
    
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
    
        $srv.ConnectionContext.LoginSecure=$false;  # Enable SQL Server Authentication
    
        $srv.ConnectionContext.set_Login("xxxxxxxx");  # Login
    
        $srv.ConnectionContext.set_Password("xxxxxxxx")  # Password
    
        # Create an operator
    
        $op = New-Object Microsoft.SqlServer.Management.Smo.Agent.Operator($srv.JobServer, "DBA Group")  # Operator name
    
        $op.emailAddress = "connie@anycompany.com;crystal@anycompany.com;waliyu@anycompany.com"  # E-mail Address
         
        $op.Create()
    
    }

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 07, 2018 2:41 AM