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

My pleasure sir.

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:

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.

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.