Coding Thread, Powershell - function returns a string, need to output to ConvertTo-HTML -Fragment in Coding and Web Development; Hi,
I have a function that queries a list of SQL servers and Databases simply to see if they are ...
7th November 2012, 10:16 AM #1 Rep Power 12
Powershell - function returns a string, need to output to ConvertTo-HTML -Fragment Hi,
I have a function that queries a list of SQL servers and Databases simply to see if they are 'alive'. If they are not the function writes to a string variable "ServerName - DB Name - FAIL `n".
This variable could be hundreds of lines long (hundreds of failed queries).
I am having trouble using the return value on that function (current a string) and outputting that to "ConvertTo-Html -Fragment" so that I can put each individual failure in a table in a HTML report.
Can anyone help?
My function is below for reference; (Ignore the if(!($DataSet -eq $null)) , I have reversed the check to show success just to generate some results :-) )
function checkDatabases # Used by databaseResponse to check that DB's respond to a query
foreach ($dbServer in $servers)
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $dbServer
foreach ($database in $srv.Databases)
$DBServerName = $srv.Name
$DBName = $database
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$DBName = $DBName.ToString()
$DBName = $DBName.Replace("`[","")
$DBName = $DBName.Replace("`]","")
$SqlConnection.ConnectionString = "Server=$DBServerName;Database=$DBName;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT * FROM sysobjects WHERE type = 'U'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
if (!($DataSet -eq $null))
$result += "FAILURE - $DBServerName - $DBName `n"
Last edited by mbedford; 7th November 2012 at 10:18 AM.
7th November 2012, 11:11 PM #2 Powershell is object oriented, it doesnt deal with strings of data like that, you must save your information into an object as below. I also rewrote your function, removing unnecissary code and adding proper error checking.
param ($Server, $Database, $Result)
$checkSQLDatabaseResult = new-object PSObject
$checkSQLDatabaseResult | add-member -type NoteProperty -Name Server -Value $Server
$checkSQLDatabaseResult | add-member -type NoteProperty -Name Database -Value $Database
$checkSQLDatabaseResult | add-member -type NoteProperty -Name Result -Value $Result
$results = @()
foreach ($serverName in $serverNames)
$server = $null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $dbServer
$databases = $server.Databases
if ($error.Count -gt 0)
$results += New-CheckSQLDatabaseResult $serverName "<null>" "ConnectFail"
foreach ($database in $databases)
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection "Server=$serverName;Database=$($database.Name);Integrated Security=True"
$sqlCmd = $sqlConnection.CreateCommand()
$sqlCmd.CommandText = "SELECT * FROM sysobjects WHERE type = 'U'"
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$dataSet = New-Object System.Data.DataSet
if ($dataSet.Tables.Count -eq 0)
$results += New-CheckSQLDatabaseResult $serverName $database.Name "QueryFail"
CheckSQLServers @("testsql1", "testsql2", "testsql3") | ConvertTo-Html | Set-Content test.html
8th November 2012, 09:05 AM #3 Rep Power 12
Chris, you are a star.
I will implement that into my system later on and see how I get on.
You can probably tell from my abysmal code that I am a complete novice when it comes to PowerShell so I appreciate the help even more.
By timbo343 in forum Scripts
Last Post: 26th September 2007, 12:24 PM
By scouselpl in forum Educational IT Jobs
Last Post: 13th June 2007, 09:24 AM
By Kyle in forum Wireless Networks
Last Post: 3rd April 2007, 11:57 AM
By timbo343 in forum Scripts
Last Post: 7th March 2007, 03:37 PM
By sidewinder in forum Windows
Last Post: 21st February 2007, 04:40 PM Thread Information Users Browsing this Thread
There are currently 1 users browsing this thread.
(0 members and 1 guests)