SQL Server cmdlets

Welcome Forums General PowerShell Q&A SQL Server cmdlets

This topic contains 7 replies, has 5 voices, and was last updated by

 
Participant
3 months, 2 weeks ago.

  • Author
    Posts
  • #110528

    Participant
    Points: 1
    Rank: Member

    I have a question regarding this sentence..."SQL PowerShell 2016 starting with the SSMS 2016 now includes 6 cmdlets for working with SQL Agent objects."

    I am trying to write a script to monitor SQL Agent job status on many servers. But I am pretty sure those new cmdlets will ONLY work when connecting to a server that has SQL 2016 installed, right? The servers on my network are 2016,2012, 2008.

    -KS

  • #110542

    Participant
    Points: 332
    Helping Hand
    Rank: Contributor

    I have not used them, specifically because I have not SQL2K16 deployed, but I'd strongly suggest you take look at this module...

    'powershellgallery.com/packages/dbatools/0.9.395'

    … and the authors article / site here:

    scheduling powershell tasks with sql agent
    'dbatools.io/agent'

  • #110552

    Participant
    Points: 355
    Helping Hand
    Rank: Contributor

    You are just performing a GET operation, so install SQL 2016 tools, import the module and see if you can connect to the SQL agent on all of the servers. But also a quick search nets:

    New PowerShell Commands from the SQL Server Team!

    with regards to the SQL Agent Cmdlets:

    One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server — only your workstation. That's because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.

  • #110596

    Participant
    Points: 123
    Rank: Participant

    * dont need to install SQL Server tools, you can install the sqlserver or pwsh (PowerShell Core) module on it's own and get everything they have.

  • #110747

    Participant
    Points: 1
    Rank: Member

    Thanks for all the replies. dbatools seems to be a "must have" for a DBA. I wrote this simple script using Find-DbaAgentJob but it only seems to run against my SQL 2014 & 2016 SQL servers. I thought it might be the powershell version on the other servers but they are at v.4 Any ideas?

    
    $allservers = "SVR2016","SVR2014","SVR2008"
    
    $countOfErrors = ($allservers | Find-DbaAgentJob -failed -Since (Get-Date).AddDays(-1) | measure-object).Count
    
    if($countOfErrors -gt 0) {
    
    $allservers | Find-DbaAgentJob -failed -Since (Get-Date).AddDays(-1) | out-file c:\downloads\sqlAgentErrors.txt
    
    Send-MailMessage -From "sextonk@fauqhealth.org" -To ('sextonk@fauqhealth.org') -Subject "failed SQL Agent job(s) in last day" -body "Open the attachment to see the failed SQL tasks within the last day." -attachments c:\downloads\sqlAgentErrors.txt -SmtpServer "myMailServer.fauquierhospital.org"
    
    }
    
    
  • #110750

    Participant
    Points: 1
    Rank: Member

    I verified powershell remoting is enabled on the servers where my script is not working.

    #This works: 
    get-DbaAgentJob  -SqlInstance FAUQxxx01
    #This does not: 
    Find-DbaAgentJob  -isfailed -SqlInstance FAUQxxx01 -Since (Get-Date).AddDays(-10)
    

    -Kevin

  • #110753

    Participant
    Points: 895
    Helping Hand
    Rank: Major Contributor

    Are you geting any error ? or its just doesn't show anything from 2008 Server.

    • #110893

      Participant
      Points: 1
      Rank: Member

      no errors. It just doesn't return anything.

      -Kevin

The topic ‘SQL Server cmdlets’ is closed to new replies.