Error to Retrieve DB status

• DBA_007

  Ten Centuries

  Points: 1180

  More actions

  August 8, 2017 at 7:18 am

  #343957

  Hi All,
  Could someone correct the error messages in this PowerShell script used to retrieve DB status.

  $isodate=Get-Date -format s
  $isodate=$isodate -replace(":","")
  $basepath=(Get-Location -PSProvider FileSystem).ProviderPath
  $outputfile="\database_status_" + $isodate + ".html"
  $outputfilefull = $basepath + $outputfile
  $style="" $dt = new-object "System.Data.DataTable"
  foreach ($svr in get-content "D:\New folder\Servers.txt")
  { $svr $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
  $cn.Open()
  $sql = $cn.CreateCommand()
  $sql.CommandText = " SELECT @@servername,name, state_desc as Database_status FROM sys.databases "
  $rdr = $sql.ExecuteReader() $dt.Load($rdr) $cn.Close()
  }
  $dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server Database Status Report" | Set-Content $outputfilefull

  Error Messages:

  line:7 char:11
  + $style="" $dt = new-object "System.Data.DataTable"
  +    ~~~
  Unexpected token '$dt' in expression or statement.
  At line:9 char:8
  + { $svr $cn = new-object System.Data.SqlClient.SqlConnection "server=$ ...
  +   ~~~
  Unexpected token '$cn' in expression or statement.
  At line:13 char:29
  + $rdr = $sql.ExecuteReader() $dt.Load($rdr) $cn.Close()
  +          ~~~
  Unexpected token '$dt' in expression or statement.
  At line:13 char:44
  + $rdr = $sql.ExecuteReader() $dt.Load($rdr) $cn.Close()
  +               ~~~
  Unexpected token '$cn' in expression or statement.
    + CategoryInfo    : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  August 16, 2017 at 12:30 pm

  #1955555

  Need to put some parts on new lines:

  $isodate=Get-Date -format s
  $isodate=$isodate -replace(":","")
  $basepath=(Get-Location -PSProvider FileSystem).ProviderPath
  $outputfile="\database_status_" + $isodate + ".html"
  $outputfilefull = $basepath + $outputfile
  $style=""
  $dt = new-object "System.Data.DataTable"
  foreach ($svr in get-content "D:\New folder\Servers.txt")
  { $svr
  $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
  $cn.Open()
  $sql = $cn.CreateCommand()
  $sql.CommandText = " SELECT @@servername,name, state_desc as Database_status FROM sys.databases "
  $rdr = $sql.ExecuteReader()
  $dt.Load($rdr)
  $cn.Close()
  }
  $dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server Database Status Report" | Set-Content $outputfilefull