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

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: (:slight_smile: , ParentContainsErrorRecordException
    • FullyQualifiedErrorId : MethodCountCouldNotFindBest

Thanks in advance for your quick help!!

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

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 ""

}

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?

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 “”
}

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.

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.

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.

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

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!!