Script improvement advice.

• MickyD

  SSCarpal Tunnel

  Points: 4601

  More actions

  February 3, 2015 at 2:51 am

  #297309

  Hi everyone.

  Hoping someone could offer me some advice in regards to an issue I am stuck on.

  My script checks the Tempdb status , I would like the results to a .csv and also display on screen if possible.

  If possible could someone advise me on what I need to change in my script , how the options work etc.

  Any comments on how the script is working , and how I can change it would be most appreciated as I am trying to learn PowerShell.

  My current script:

  Clear-Host

  foreach ($svr in get-content "C:\PowerShell\Servers.txt")

  {

  function Test-SQLConnection{

  param([parameter(mandatory=$true)][string[]] $Instances)

  $return = @()

  foreach($InstanceName in $Instances){

  $row = New-Object –TypeName PSObject –Prop @{'InstanceName'=$InstanceName;'StartupTime'=$null}

  try{

  $check=Invoke-Sqlcmd -ServerInstance $InstanceName -Database TempDB -Query "SELECT @@SERVERNAME as Name,Create_Date FROM sys.databases WHERE name = 'TempDB'" -ErrorAction Stop -ConnectionTimeout 3

  $row.InstanceName = $check.Name

  $row.StartupTime = $check.Create_Date

  }

  catch{

  #do nothing on the catch

  }

  finally{

  $return += $row

  }

  }

  return $return

  }

  Test-SQLConnection -Instances $svr

  }

  thank you in advance for any advice offered.

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  February 4, 2015 at 11:55 pm

  #1775578

  If you changed the line:

  Test-SQLConnection -Instances $svr

  to

  $testResults = Test-SQLConnection -Instances $svr

  then you can create a CSV file with the following line:

  $testResults | Export-Csv "C:\testResults.csv"

  and output it to the screen as follows:

  Write-Host "Connection test results: " $testResults

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• MickyD

  SSCarpal Tunnel

  Points: 4601

  More actions

  February 5, 2015 at 1:46 am

  #1775594

  Gaz

  Thanks for your reply. Will test it out today.

  Cheers