December 19, 2011 at 10:37 am
I did the same thing and it worked.
December 19, 2011 at 2:53 pm
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
December 19, 2011 at 3:40 pm
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?
December 19, 2011 at 10:10 pm
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
December 19, 2011 at 10:36 pm
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!!
December 19, 2011 at 11:03 pm
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:
Hope that helps you.
Wolraad
December 20, 2011 at 12:16 am
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
December 20, 2011 at 3:15 am
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
December 20, 2011 at 4:57 am
SQLRNNR (12/19/2011)
Thanks for the nice article.
My pleasure sir.
December 20, 2011 at 5:01 am
kunalpiyush (12/20/2011)
HiI 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.
December 20, 2011 at 6:10 am
@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
December 20, 2011 at 6:43 am
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.
December 20, 2011 at 8:14 am
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
December 20, 2011 at 8:20 am
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. 🙂
December 20, 2011 at 8:41 am
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