Send email from Powershell with attachment

This post  describes how to call  to add an email attachment using Powershell.

In an earlier post - SQL Server – Send email using Powershell – the new-object Net.Mail.SmtpClient() Class was used.

To add an email attachment , use New-Object System.Net.Mail.MailMessage and New-Object System.Net.Mail.Attachment, which are part of the .Net Framework

The example script includes creating the variables, the function and calling the function .

 

$emailFrom = "jvamvas@sqlserver-dba.com"
$emailTo = "jvamvas@sqlserver-dba.com"
$subject = "SQLServer-DBA.com: Powershell Function calling an SMTP server with attachment"
$body = "SQLServer-DBA.com : Send an email through SMTP in Powershell with attachment "
$smtpServer = "mysmtpserver"
$filePath = "C:\sql_server_health_sqlserver_jobs_2012-02.html"


Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body
# initiate email attachment 
$emailAttach = New-Object System.Net.Mail.Attachment $filePath
$email.Attachments.Add($emailAttach) 
#initiate sending email 
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}

#Call Function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

 

See Also

Powershell - run script on all sql servers

Author: Jack Vamvas (http://www.sqlserver-dba.com)

List failed SQL server Jobs with Powershell

This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file.

 It is only one extra step to send this file as an attachment.

The example script , returns failed SQL Server Agent Jobs within the last 48 hrs, for a SQL Server Instance.

 

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$instancepath=$basepath + "\instances.txt"
$outputfile="\logs\sql_server_health_sqlserver_jobs_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile
#invoke stylesheet
. .\modules\stylesheet.ps1
 
$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{

$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "SELECT sjh.server,sj.name, sjh.message, sjh.run_date, sjh.run_time , ‘http://www.sqlserver-dba.com/contact-me/’ as Explain
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4) 
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid 
WHERE    DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 48
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle | Set-Content $outputfilefull

 

See Also

SQL Server – Send email using Powershell

SQL Agent Jobs – Schedule in seconds

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

List SQL Server Instances using Powershell and Get-ItemProperty

This post explains how to list SQL Server Instances installed on the current server, using the Powershell Get-ItemProperty Cmdlet.

The Get-ItemProperty Cmdlet gets the properties of a specific item.

In this example, the command displays the Name and Data from the registry entries in the SQL registry subkey.Notice the  Powershell drive : HKLM.  It’s mapped to the HKEY_LOCAL_MACHINE .

 

Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'

 

See Also

SQL Server – Send email using Powershell

SQL Server Version with Powershell

SQL Server – COPY TABLE from another SQL Server

To copy a table from another SQL Server Instance is easy. Create a Linked Server on the destination server and reference the Linked Server in the SELECT statement.

1) Create linked server

/****** Object:  LinkedServer [SERVER1\SQL_SERVER_INSTANCE_1]    Script Date: 02/02/2012 09:29:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\SQL_SERVER_INSTANCE_1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER1\SQL_SERVER_INSTANCE_1',@useself=N'False',@locallogin=NULL,@rmtuser=N'MY_MIGRATION',@rmtpassword='########'
GO

 2)   On the destination server

 

--SELECT * into  from   

--Example:  Using the Linked server

SELECT * INTO my_test_table_copy FROM [SERVER1\SQL_SERVER_INSTANCE_1].MY_DB.dbo.my_test_table

 

See Also

SQL SELECT INTO

SQL Server Linked Servers and User permissions

SQL Server – Send email using Powershell

This article explains how to send an email through Powershell. 

 Very useful if you have a requirement to automate your scripts and send notifications .

 The example is calling the SmtpClient Class , which is part of the .Net Framework.

You can call a Function –  which makes your scripting   more modular.

 

#variables
$emailFrom = "JackVamvas@sqlserver-dba.com"
$emailTo = "dba@sqlserver-dba.com"
$subject = "SQLServer-DBA.com: Powershell Function calling an SMTP server"
$body = "SQLServer-DBA.com : Send an email through SMTP in Powershell"
$smtpServer = "aservername"

#create a function
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer)
{
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)
}

#call the function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer

 

See Also

Powershell - run script on all sql servers

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)