Cannot find an overload for "Round" and the argument count: "2".

This topic contains 9 replies, has 4 voices, and was last updated by  DBA421 10 months, 2 weeks ago.

  • Author
    Posts
  • #61612

    DBA421
    Participant

    Hello All,

    Hope everyone is doing well.

    I'm getting the following error message while executing the powershell script ..Requesting you please help me to resolve this issue.
    Script here is :

    Function writeServiceInfo
    {
    param($fileName,$machinename,$dbname,$tbname,$ixname,$dbfrg,$dbpc)
    Add-Content $filename ""
    Add-Content $filename "$machineName"
    Add-Content $filename "$dbname"
    Add-Content $filename "$tbname"
    Add-Content $filename "$ixname"
    $frg = [Math]::Round($dbfrg,2)
    If ($frg -gt "30")

    Error message

    Cannot find an overload for "Round" and the argument count: "2".
    Cannot find an overload for "Round" and the argument count: "2".
    At line:108 char:2
    + $frg = [Math]::Round($dbfrg,2)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

    Thanks in advance for your quick help!!

  • #61626

    Olaf Soyk
    Participant

    Your variable $dbfrg probably contains more than one value. Make sure that you have only one [decimal] or [single] or [double] value in it.

    • #61645

      DBA421
      Participant

      Thank you Olaf for your update,here is my code,i have not mentioned any parameters here,is it possible to change the value if we don't have exact value...Please suggest how i can proceed further on this

      Ex: Is it possible to change the value $frg = [Math]::Round($dbfrg/102222,2) — i tried it fails

      Function writeServiceInfo
      {
      param($fileName,$machinename,$dbname,$tbname,$ixname,$dbfrg,$dbpc)
      Add-Content $filename ""
      Add-Content $filename "$machineName"
      Add-Content $filename "$dbname"
      Add-Content $filename "$tbname"
      Add-Content $filename "$ixname"
      $frg = [Math]::Round($dbfrg,2)
      If ($frg -gt "30"){
      Add-Content $filename "$frg"}
      else {
      Add-Content $filename "$frg" }

      If ($dbpc -gt "1000"){
      Add-Content $filename "$dbpc"}
      else {
      Add-Content $filename "$dbpc" }

      Add-Content $filename ""
      }

  • #61653

    Olaf Soyk
    Participant

    It depends on what you have in your variable $dbfrg. If it contains mor than one value you could run into issues. Here you can see the documentation of the method Math.Round.
    What do you have in your variable $dbfrg?

    • #61660

      DBA421
      Participant

      Thank you Olaf for your quick update.I have not declare any variable.I have pasted my code here as below.can you please have a look into this. and guide me how i can procced further on this

      Here is my code.$DBA = "C:\DBA.htm"
      $serverlist = "C:Test_Servers.txt"
      $QueryTimeout = 0

      New-Item -ItemType file $DBA -Force

      function Invoke-Sqlcmd2 ($server,$database,$query)
      {
      $conn=new-object System.Data.SqlClient.SQLConnection
      $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
      $conn.Open()
      $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
      $cmd.CommandTimeout=$QueryTimeout
      $ds=New-Object system.Data.DataSet
      $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
      [void]$da.fill($ds)
      $conn.Close()
      $ds.Tables[0]
      }

      Function writeHtmlHeader
      {
      param($fileName)
      $date = ( Get-Date ).ToString('yyyy/MM/dd – hh:mm')
      Add-Content $fileName ""
      Add-Content $fileName ""
      Add-Content $fileName ""
      Add-Content $fileName 'Database Fragmentation'
      add-content $fileName "
      add-content $fileName ""
      add-content $fileName ""
      Add-Content $fileName ""
      Add-Content $fileName ""
      add-content $fileName ""
      add-content $fileName ""
      add-content $fileName ""
      add-content $fileName "Database Fragmentation – $date"
      add-content $fileName ""
      add-content $fileName ""
      add-content $fileName ""
      }

      Function writeTableHeader
      {

      param($fileName)
      Add-Content $fileName ""
      Add-Content $fileName "Server Name"
      Add-Content $fileName "Database Name"
      Add-Content $fileName "Table Name"
      Add-Content $fileName "Index Name"
      Add-Content $fileName "Average Fragmentation"
      Add-Content $fileName "Page Count"
      Add-Content $fileName ""
      }

      Function writeHtmlFooter
      {
      param($fileName)
      Add-Content $fileName ""
      Add-Content $fileName ""
      }

      Function sendEmail
      { param($from,$to,$subject,$smtphost,$htmlFileName)
      $body = Get-Content $htmlFileName
      $smtp= New-Object System.Net.Mail.SmtpClient $smtphost
      $msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
      $msg.isBodyhtml = $true
      $smtp.send($msg)

      }

      Function writeServiceInfo
      {
      param($fileName,$machinename,$dbname,$tbname,$ixname,$dbfrg,$dbpc)
      Add-Content $filename ""
      Add-Content $filename "$machineName"
      Add-Content $filename "$dbname"
      Add-Content $filename "$tbname"
      Add-Content $filename "$ixname"
      $frg = [Math]::Round($dbfrg,2)
      If ($frg -gt "30"){
      Add-Content $filename "$frg"}
      else {
      Add-Content $filename "$frg" }

      If ($dbpc -gt "1000"){
      Add-Content $filename "$dbpc"}
      else {
      Add-Content $filename "$dbpc" }

      Add-Content $filename ""
      }

      # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
      $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
      if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
      }

      # Handle any errors that occur
      Trap {
      # Handle the error
      $err = $_.Exception
      write-host $err.Message
      while( $err.InnerException ) {
      $err = $err.InnerException
      write-output $err.Message
      };
      # End the script.
      break
      }

      writeHtmlHeader $DBA

      foreach ($server in Get-Content $serverlist)
      {
      Add-Content $DBA ""
      Add-Content $DBA ""
      Add-Content $DBA " $server "
      Add-Content $DBA ""
      writeTableHeader $DBA

      # Connect to the specified instance
      $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server

      # Get the databases for the instance, and iterate through them
      $dbs = $s.Databases
      foreach ($db in $dbs) {
      # Check to make sure the database is not a system database, and is accessible
      if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True) {
      # Store the database name for reporting
      $dbname = $db.Name
      $dbid = [string]$db.ID

      $tbs = $db.Tables
      foreach ($tb in $tbs) {
      # Store the table name for reporting
      $tbname = $tb.Name
      $tbid = [string]$tb.ID

      $ixs = $tb.Indexes
      foreach ($ix in $ixs) {
      # We don't want to process XML indexes
      if ($ix.IsXmlIndex -eq $False) {
      # Store the index name for reporting
      $ixname = $ix.Name
      $ixid = [string]$ix.ID

      # Get the Fragmentation and page count information
      $q = @"
      select avg_fragmentation_in_percent, page_count
      from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
      "@
      $res = invoke-sqlcmd2 $server $dbname $q
      $frval = $res.avg_fragmentation_in_percent
      $pgcnt = $res.page_count
      writeServiceinfo $DBA $server $dbname $tbname $ixname $frval $pgcnt
      }
      }
      }

      }
      }
      Add-Content $DBA ""
      }

    • #61665

      Olaf Soyk
      Participant

      Sorry, that is just too much and too badly formatted for me to dig into it. If you post code you should format it as code. And you should indent your code to make it better readable and understandable.

  • #61702

    Fredrik Kacsmarck
    Participant

    Not an answer to the problem per say but what is the content of $dbfrg?
    E.g. just do a write-output or out-file of that content so you know what the initial content is.
    If it's something the Math method can't deal with you're going to need some exception code.

    • #61768

      DBA421
      Participant

      Thank you Fredrik and rob for your quick help.my request is need Powershell script to find the Fragmentation details greater than 30% for a list of servers and the results needs to be exported in excel. We need thi sresults over the email(MSSQL Server DB)

      $dbfrg value is float and its like 99.9 like that..

      If possible can you please review my code,Not sure which part i have to remove it.

      $DBA = "C:\test\DBA_test.htm"
      $serverlist = "C:\servers.txt"
      $QueryTimeout = 0

      New-Item -ItemType file $DBA_test -Force

      function Invoke-Sqlcmd2 ($server,$database,$query)
      {
      $conn=new-object System.Data.SqlClient.SQLConnection
      $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
      Write-host "connection information:"
      $connection #List connection information
      Write-host "connect to database successful."
      $conn.Open()
      $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
      $cmd.CommandTimeout=$QueryTimeout
      $ds=New-Object system.Data.DataSet
      $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
      [void]$da.fill($ds)
      $conn.Close()
      $ds.Tables[0]
      }

      Function writeHtmlHeader
      {
      param($fileName)
      $date = ( Get-Date ).ToString('yyyy/MM/dd – hh:mm')

      }

      Function writeTableHeader
      {

      param($fileName)
      Add-Content $fileName ""
      Add-Content $fileName "Server Name"
      Add-Content $fileName "Database Name"
      Add-Content $fileName "Table Name"
      Add-Content $fileName "Index Name"
      Add-Content $fileName "Average Fragmentation"
      Add-Content $fileName "Page Count"
      Add-Content $fileName ""
      }

      Function writeHtmlFooter
      {
      param($fileName)

      }

      Function sendEmail
      { param($from,$to,$subject,$smtphost,$htmlFileName)
      $body = Get-Content $htmlFileName
      $smtp= New-Object System.Net.Mail.SmtpClient $smtphost
      $msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
      $msg.isBodyhtml = $true
      $smtp.send($msg)

      }

      Function writeServiceInfo
      {
      param($fileName,$machinename,$dbname,$tbname,$ixname,$dbfrg,$dbpc)
      Add-Content $filename ""
      Add-Content $filename "$machineName"
      Add-Content $filename "$dbname"
      Add-Content $filename "$tbname"
      Add-Content $filename "$ixname"
      $frg = [Math]::Round($dbfrg,2)
      If ($frg -gt "30"){
      Add-Content $filename "$frg"}
      else {
      Add-Content $filename "$frg" }

      If ($dbpc -gt "1000"){
      Add-Content $filename "$dbpc"}
      else {
      Add-Content $filename "$dbpc" }

      Add-Content $filename ""
      }

      # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
      $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
      if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
      }

      # Handle any errors that occur
      Trap {
      # Handle the error
      $err = $_.Exception
      write-host $err.Message
      while( $err.InnerException ) {
      $err = $err.InnerException
      write-output $err.Message
      };
      # End the script.
      break
      }

      writeHtmlHeader $DBA_test

      foreach ($server in Get-Content $serverlist)
      {
      Add-Content $DBA_Test ""
      Add-Content $DBA_test ""
      Add-Content $DBA_test " $server "
      Add-Content $DBA_test ""
      writeTableHeader $DBA_test

      # Connect to the specified instance
      $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server

      # Get the databases for the instance, and iterate through them
      $dbs = $s.Databases
      foreach ($db in $dbs) {
      # Check to make sure the database is not a system database, and is accessible
      if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True) {
      # Store the database name for reporting
      $dbname = $db.Name
      $dbid = [string]$db.ID

      $tbs = $db.Tables
      foreach ($tb in $tbs) {
      # Store the table name for reporting
      $tbname = $tb.Name
      $tbid = [string]$tb.ID

      $ixs = $tb.Indexes
      foreach ($ix in $ixs) {
      # We don't want to process XML indexes
      if ($ix.IsXmlIndex -eq $False) {
      # Store the index name for reporting
      $ixname = $ix.Name
      $ixid = [string]$ix.ID

      # Get the Fragmentation and page count information
      $q = @"
      select avg_fragmentation_in_percent, page_count
      from sys.dm_db_index_physical_stats($dbid, $tbid, $ixid, NULL, NULL)
      "@
      $res = invoke-sqlcmd2 $server $dbname $q
      $frval = $res.avg_fragmentation_in_percent
      $pgcnt = $res.page_count
      writeServiceinfo $DBA_test $server $dbname $tbname $ixname $frval $pgcnt
      }
      }
      }

      }
      }
      Add-Content $DBA_test ""
      }

      Writehtmlfooter $DBA
      $date = ( Get-Date ).ToString('yyyy/MM/dd – hh:mm')
      sendEmail xxxxxxx

  • #61716

    Rob Simmers
    Participant

    It looks like you are attempting to create a report. First, you should concentrate on collecting data and getting it into a PSObject. Once you have the data, what you are doing for reporting is not HTML. You should look at Creating HTML Reports in PowerShell in the eBooks link above. You can also perform functions like rounding in SQL to have all the data formatted and ready to build your report. Additionally, when you are building the report keep in mind what you plan to do with it. If you are going to email it, you should start with a mail friendly HTML template, because email clients are finicky with HTML.

  • #61774

    DBA421
    Participant

    Thank you Fredrik and rob for your quick help.my request is need Powershell script to find the Fragmentation details greater than 30% for a list of servers and the results needs to be exported in excel. We need thi sresults over the email(MSSQL Server DB)

    $dbfrg value is float and its like 99.9 like that..

    If possible can you please review my code once.

    Thanks in advance!!

You must be logged in to reply to this topic.