+ Post New Thread
Results 1 to 3 of 3
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 ...
  1. #1

    Join Date
    Jul 2009
    Location
    Staffs
    Posts
    76
    Thank Post
    10
    Thanked 9 Times in 7 Posts
    Rep Power
    13

    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 :-) )

    Code:
    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
    
    }
    Last edited by mbedford; 7th November 2012 at 11:18 AM.

  2. #2

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    22
    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.

    Code:
    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 "<null>" "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

  3. #3

    Join Date
    Jul 2009
    Location
    Staffs
    Posts
    76
    Thank Post
    10
    Thanked 9 Times in 7 Posts
    Rep Power
    13
    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



SHARE:
+ Post New Thread

Similar Threads

  1. Need a script to run programs
    By timbo343 in forum Scripts
    Replies: 9
    Last Post: 26th September 2007, 01:24 PM
  2. It feels like Baghdad High...I need a move to a nice school!
    By scouselpl in forum Educational IT Jobs
    Replies: 1
    Last Post: 13th June 2007, 10:24 AM
  3. Need help getting to grips with subnetting
    By Kyle in forum Wireless Networks
    Replies: 26
    Last Post: 3rd April 2007, 12:57 PM
  4. Replies: 4
    Last Post: 7th March 2007, 04:37 PM
  5. Replies: 5
    Last Post: 21st February 2007, 05:40 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •