February 6, 2020 at 1:39 pm
I found this Sources from: https://solutioncenter.apexsql.com/multi-server-script-to-automatically-monitor-sql-server-availability/
I have this exception:
ComputerName ComputerName MSSQLSERVER
tempdb creation time is 4/2/2020 7:26:57 PM SQL connection to ComputerName
Exception when calling "ExecuteNonQuery" with "0" argument (s): "String or binary data would be truncated. The statement has been terminated."
On the line: 118 character: 1
+ $ Command.ExecuteNonQuery () | out-null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo: NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId: SqlException
February 6, 2020 at 1:42 pm
Function Get-ServiceInfo {
Param(
[String]$Server,
[String]$SQLInstance,
[String]$SQLService,
[String]$SMTP,
[String]$To
)
# Get the current datetime
$logdate = (Get-Date).ToString('MM/dd/yyyy hh:mm:ss')
#Function created to send email
Function sendEmail
{
param($from,$to,$subject,$smtp,$body)
[string]$receipients="$to"
$body = $body
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$smtpServer = $smtp
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
}
#Test the server connection
if((test-connection -ComputerName $Server -count 1 -ErrorAction SilentlyContinue))
{
#Check the SQL Service
$service = Get-WmiObject Win32_Service -ComputerName $server |where-object {$_.name -eq "$SQLService" -and $_.State -eq 'Running'}
#Check for the instance availability
if ($Service -ne $NULL)
{
#Query the tempdb database creation time
$result = Invoke-Sqlcmd -Query "SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'" -ServerInstance $SQLInstance
if ($result)
{
Write-Host "tempdb creation time is $($result.create_date) SQL connection to $SQLInstance"
$crdate=$($result.create_date)
$props += New-Object PSObject -Property @{
Servername = $server
name = $service.name
tempdbCreationTime=$crdate.ToString('MM/dd/yyyy hh:mm:ss')
Status = $service.Status
startmode = $service.startmode
state = $service.state
serviceaccount=$service.startname
DisplayName =$service.displayname
LogDate=$logdate
}
}
}
else
{
$props += [pscustomobject]@{
Servername = $server
tempdbCreationTime='NA'
name = 'MSSQLServer'
Status = 'Not Running'
startmode = 'NA'
state = 'NA'
serviceaccount='NA'
DisplayName ='SQL Server'
LogDate=$logdate}
$status = "Critical"
$priority = "HIGH"
$body = @"
This is to Notify that SQL Server service is not running!
On the $server, the SQL $SQLInstance instance, the servic$SQLService service is Stopped. Please assign an $priority priority ticket to the Respective DBA team.
-This is an auto generated email generated by the PowerShell script. Please do not reply!!
"@
sendEmail pjayaram@sqlShack.com "$to" "SQL Service On $server is not running!" $SMTP $body
}
}
else
{
$props += [pscustomobject]@{
Servername = $server
tempdbCreationTime='NA'
name = 'SQL Server'
Status = 'Server Not Responding'
startmode = 'NA'
state = 'NA'
serviceaccount='NA'
DisplayName ='NA'
LogDate=$logdate}
$status = "Critical"
$priority = "HIGH"
$body = @"
This is to Notify that SQL Server is not responding!
The $server is not responding . Please assign an $priority priority ticket to the Respective DBA team.
-This is an auto generated email generated by the PowerShell script. Please do not reply!!
"@
sendEmail pjayaram@appvion.com "$to" "SQL Service On $server is not running!" $SMTP $body
}
#maintain the output of columns in a specific order
$data=$props|select-object servername,name,tempdbCreationTime,status,startmode,serviceaccount,Displayname,logdate
# Build trusted Connection to the SQL Server and a database
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=ComputerName;Database=sql_server_monitor;trusted_connection=true;"
#Open the connection
$Conn.Open()
# prepare the SQL command. This is something like building a dynamic SQL
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Conn
$data | foreach {
$Command.CommandText = "INSERT INTO tbl_serveravailability (Servername, TempdbCreationTime, Name, status, startmode, serviceaccount, DisplayName, LogDate) VALUES ('$($data.servername)','$($data.TempdbCreationTime)','$($data.Name)','$($data.status)','$($data.startmode)','$($data.serviceaccount)','$($data.DisplayName)','$($data.LogDate)')"
#Insert the prepared SQL statement to the Central repository
$Command.ExecuteNonQuery() | out-null
}
}
#Input file - lists all the SQL Servers
$filepath='\\ComputerName\c$\Server\serverlist.csv'
# Import the CSV file and Loop through each server
Import-CSV $filepath | Foreach-Object{
write-host $_.ServerName $_.InstanceName $_.SQLServiceName
Get-ServiceInfo -server $_.ServerName -SQLInstance $_.InstanceName -SQLService $_.SQLServiceName -SMTP 'mail.sqlshackdemo.com' -to 'vpmaciel@gmail.com'
}
February 6, 2020 at 1:43 pm
How to solve this problem ?
February 6, 2020 at 1:46 pm
Is there a column on tbl_serveravailability which is not wide enough to accept the data which is being inserted into it?
February 6, 2020 at 1:55 pm
I will check if there is a column on tbl_serveravailability which is not wide enough to accept the data which is being inserted into it.
February 6, 2020 at 2:16 pm
Thank you so much. I change all columns size. It's solved.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy