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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
February 6, 2020 at 2:18 pm
Thank you so much. I change all columns size. It's solved.
Excellent, thanks for posting back.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply