Powershell - function returns a string, need to output to ConvertTo-HTML -Fragment

[mbedford]

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
{
param ($servers)
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.ToString
           $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
		$SqlAdapter.Fill($DataSet)
		$SqlConnection.Close()
			
		if (!($DataSet -eq $null))
		{
			$result += "FAILURE - $DBServerName - $DBName `n"
			
		}
		
		
	}
	
}

return $result

}

Edited November 7, 2012 by mbedford

[ChrisMiles]

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.

 

function New-CheckSQLDatabaseResult()
{
 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

 return $checkSQLDatabaseResult
}
function CheckSQLServers
{
param ($serverNames)

$results = @()

foreach ($serverName in $serverNames)
{
	$error.Clear()
	
	$server = $null
	$server = New-Object Microsoft.SqlServer.Management.Smo.Server $dbServer
	
	$databases = $server.Databases
	
	if ($error.Count -gt 0)
	{
		$results += New-CheckSQLDatabaseResult $serverName "" "ConnectFail"
		continue;
	}
	
	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
		$sqlAdapter.Fill($dataSet)
		$sqlConnection.Close()
		
		if ($dataSet.Tables.Count -eq 0)
		{
			$results += New-CheckSQLDatabaseResult $serverName $database.Name "QueryFail"
		}
	}
}

return $results
}

CheckSQLServers @("testsql1", "testsql2", "testsql3") | ConvertTo-Html | Set-Content test.html

[mbedford]

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.

 

Thanks again,

Mike