Automating Daily Checks with Powershell

  • I did the same thing and it worked.

  • line 455 is missing:

    $results5 |convertto-html -Title

    it should read:

    $results5 |convertto-html -Title "Backup Status" -Head $a -Body "<H2> ERRORSoutLIST </H2>" | out-file $workingdir\errors.html

  • Does this not work on SQL 2005? I don't see any reason why it shouldn't unless Powershell has issues with 2005?

    I can't seem to get it working at all. I'm having an issue where it seems it doesn't have permission to run:

    You cannot call a method on a null-valued expression.

    At Z:\DBA Stuff\DBA Reports\htmlreports.ps1:44 char:24

    + $line = $line.TrimEnd <<<< ();

    + CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    FAILED TO CONNECT TO INSTANCE SERVERNAMEHERE

    If I change the Invoke-sqlcmd to contain a username/password like so:

    $databaseUserName="test"

    $databasePassword="test"

    Invoke-sqlcmd -Username $databaseUserName -Password $databasePassword

    Then I still get an error:

    Invoke-Sqlcmd : Login failed for user 'test'.

    At Z:\DBA Stuff\DBA Reports\htmlreports.ps1:56 char:14

    + Invoke-sqlcmd <<<< -Username $databaseUserName -Password $databasePassword -ServerInstance $hostserver -Database $hostdb -Query "exec spGetServerListHTML prod"|out-file $workingdi

    r\Prodserverlist.txt

    + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException

    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

    Ideas?

  • Thanks for the nice article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I was researching how to roll my own solution like this using a job and tsql itself.

    I'm totally going to give this a shot and look into taking it further to check on more things! Thanks very much!!

  • Hi carlowahlstedt

    Could it be that you have chosen to store the HTMLReports table and stored procs in a database other than MSDB? I did and got the same "FAILED TO CONNECT..." error message. I made the following changes and got rid of the error message:

  • in htmlreport.ps1 on lines 265 and 347 change "$hostdb.dbo.backupset" to "msdb.dbo.backupset".
  • in the stored proc getserverlisthtml change "from msdb.dbo.htmlreports" to "from htmlreports".
  • in the stored proc Getdeschtmlreports change "from msdb.dbo.htmlreports" to "from htmlreports".
  • Hope that helps you.

    Wolraad

  • Hi

    I see that the script is using invoke-sqlcmd/sqlcmd to output results to a txt file. I am inquisitive to know as to why we can't save the results in a powershell dataset. Wouldnt iterating the loop in the dataset a better option and avoid managing temp files alltogether (creating,trimming, removing)?

    Is using temp files a better option in terms of performance/manageability than datasets. Please advise.

    Kunal

  • I am getting loads of errors... any help please:

    ------------------------------------------------------

    The term 'else' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelli

    ng of the name, or if a path was included, verify that the path is correct and try again.

    At line:1 char:5

    + else <<<<

    + CategoryInfo : ObjectNotFound: (else:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

    -----------------------------------------------------------------------------------------------------------

    Set-Location : Cannot process argument because the value of argument "path" is null. Change the value of argument "path

    " to a non-null value.

    At line:1 char:3

    + cd <<<< $sqlpsPath -ea 0

    + CategoryInfo : InvalidArgument: (:) [Set-Location], PSArgumentNullException

    + FullyQualifiedErrorId : ArgumentNull,Microsoft.PowerShell.Commands.SetLocationCommand

    ---------------------------------------------------------------------------------------------------------------

    You cannot call a method on a null-valued expression.

    At line:11 char:28

    + $line = $line.TrimEnd <<<< ();

    + CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    ----------------------------------------------------------------------------------------------------------------

    You cannot call a method on a null-valued expression.

    At line:11 char:28

    + $line = $line.TrimEnd <<<< ();

    + CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    ---------------------------------------------------------------------------------------------------------------------

    Import-Csv : Cannot open file "C:\Data\Powershell\HTMLReports\Working\errorlogcombined.txt".

    At line:1 char:11

    + import-csv <<<< $workingdir\errorlogcombined.txt -delimiter "|" |export-csv $workingdir\Errorlogs-past72hrs.csv

    + CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException

    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

    -------------------------------------------------------------------------------------------------------------

    Remove-Item : Cannot find path 'C:\Data\Powershell\HTMLReports\Working\errorlogcombined.txt' because it does not exist.

    At line:1 char:12

    + remove-item <<<< $workingdir\errorlogcombined.txt

    + CategoryInfo : ObjectNotFound: (C:\Data\Powersh...logcombined.txt:String) [Remove-Item], ItemNotFoundEx

    ception

    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.RemoveItemCommand

    ---------------------------------------------------------------------------------------------------

    You must provide a value expression on the right-hand side of the '-' operator.

    At line:1 char:18

    + "Backup Status" - <<<< Head $a -Body "<H2> ERRORSLIST </H2>"|out-file $workingdir\errors.html

    + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException

    + FullyQualifiedErrorId : ExpectedValueExpression

    ------------------------------------------------------------------------------------------------------

    Get-Item : Cannot find path 'C:\Documents and Settings\ea_kigiri\backupreport.html' because it does not exist.

    At line:7 char:20

    + $fileObj = get-item <<<< $fileName

    + CategoryInfo : ObjectNotFound: (C:\Documents an...ckupreport.html:String) [Get-Item], ItemNotFoundExcep

    tion

    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand

    You cannot call a method on a null-valued expression.

    At line:11 char:34

    + $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')

    + CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.

    At line:23 char:37

    + $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')

    + CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    Rename-Item : Cannot rename because item at 'backupreport.html' does not exist.

    At line:24 char:15

    + rename-item <<<< "$fileName" "$nameOnly-$DateStamp$extOnly"

    + CategoryInfo : InvalidOperation: (:) [Rename-Item], PSInvalidOperationException

    + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.RenameItemCommand

    -------------------------------------------------------------------------------------------------------

    Get-Item : Cannot find path 'C:\Documents and Settings\ea_kigiri\Errorlogs-past72hrs.csv' because it does not exist.

    At line:7 char:20

    + $fileObj = get-item <<<< $fileName

    + CategoryInfo : ObjectNotFound: (C:\Documents an...s-past72hrs.csv:String) [Get-Item], ItemNotFoundExcep

    tion

    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand

    You cannot call a method on a null-valued expression.

    At line:11 char:34

    + $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')

    + CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.

    At line:23 char:37

    + $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')

    + CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    Rename-Item : Cannot rename because item at 'Errorlogs-past72hrs.csv' does not exist.

    At line:24 char:15

    + rename-item <<<< "$fileName" "$nameOnly-$DateStamp$extOnly"

    + CategoryInfo : InvalidOperation: (:) [Rename-Item], PSInvalidOperationException

    + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.RenameItemCommand

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • SQLRNNR (12/19/2011)


    Thanks for the nice article.

    My pleasure sir.

  • kunalpiyush (12/20/2011)


    Hi

    I see that the script is using invoke-sqlcmd/sqlcmd to output results to a txt file. I am inquisitive to know as to why we can't save the results in a powershell dataset. Wouldnt iterating the loop in the dataset a better option and avoid managing temp files alltogether (creating,trimming, removing)?

    Is using temp files a better option in terms of performance/manageability than datasets. Please advise.

    Kunal

    Hi Kunal,

    I started this project as an effort to teach myself Powershell. I certainly wouldnt claim this to be a perfect solution. I seem to recall some difficulty getting dataset results formatted properly, but Im not 100pct sure.

  • @wolraad Thanks!

    That got it creating useful files. I'm still getting several errors but it seems to be working now. It seems the article doesn't point to a specific database but requires msdb. Awesome article however. Kudos on the huge powershell. I, like many others, don't really know powershell. This would have taken me a good amount of time to create.

    Here's a list of the errors if anyone else has ideas:

    You cannot call a method on a null-valued expression.

    At Z:\DBA Stuff\DBA Reports\htmlreports.ps1:44 char:24

    + $line = $line.TrimEnd <<<< ();

    + CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    The term '.\parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

    At Z:\DBA Stuff\DBA Reports\htmlreports.ps1:203 char:21

    + .\parseerrorlog.bat <<<< $instance

    + CategoryInfo : ObjectNotFound: (.\parseerrorlog.bat:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

    Import-Csv : Cannot open file "Z:\DBA Stuff\DBA Reports\errorlogcombined.txt".

    At Z:\DBA Stuff\DBA Reports\htmlreports.ps1:219 char:11

    + import-csv <<<< $workingdir\errorlogcombined.txt -delimiter "|" |export-csv $workingdir\Errorlogs-past72hrs.csv

    + CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException

    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

    Remove-Item : Cannot find path 'Z:\DBA Stuff\DBA Reports\errorlogcombined.txt' because it does not exist.

    At Z:\DBA Stuff\DBA Reports\htmlreports.ps1:223 char:12

    + remove-item <<<< $workingdir\errorlogcombined.txt

    + CategoryInfo : ObjectNotFound: (Z:\DBA Stuff\DB...logcombined.txt:String) [Remove-Item], ItemNotFoundException

    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.RemoveItemCommand

  • When i created the script initially, i had hardcoded msdb. When i thought about publication, i figured you might choose differently, so went back, created the db variable, and intended to replace all the hardcoded refeneces to msdb with the variable... Guess i missed a couple.

  • Offering a link to download the script as a whole, in place of copy/pasting, might be useful for folks. Good article though.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • I am getting these same errors. I have tried to resolve the problems without success. If anyone has a solution, let me know. Thanks. I am using Windows 2008 and SQL 2008. I found many type o and other errors, so I am sure it is something that just needs tweeking. It is a great idea, if it worked. 🙂

  • VanishingW (12/20/2011)


    I am getting these same errors. I have tried to resolve the problems without success. If anyone has a solution, let me know. Thanks. I am using Windows 2008 and SQL 2008. I found many type o and other errors, so I am sure it is something that just needs tweeking. It is a great idea, if it worked. 🙂

    Oh, it works. See previous post about the missing line w $results5 that should be all that is missing. I am on vacation this week and most of next, but can post a link to the complete script next thurs.

  • Viewing 15 posts - 16 through 30 (of 47 total)

    You must be logged in to reply to this topic. Login to reply