How to send query content as xml zip file to an email according to row

Transact-SQL
1

Hi All,

I have a table with 2 column: Email, Content.

The Content data look like this:-

declare @table Table (
Email nvarchar(max),
Content notes
)

insert into @table
select 'test@gmail.com','<?xml version="1.0"?>
<licenseAttachment ver="1.0">
	<entitlements>
		<entitlement id="XXX-84553-122222">
			<owner name="Corporation 128" siteID="84553" />
			<activations>
				<activation id="XXX-QY59-KYST-H681">
					<partNumber>PltSCP-28-T-22</partNumber>
					<catalogName>Plt 2022 Control Client Unlimited Tags</catalogName>
					<serialNumber>XXX-QY59-KYST-H681</serialNumber>
					<quantity>7</quantity>
				</activation>
			</activations>
		</entitlement>
	</entitlements>
</licenseAttachment>'

This can be multiple row. So each content (row data) have to send seprately email based on email column as recipients.

How can I send an email with this content as attachment .xml.zip?

Please advise.

Thanks.

Regards,
Micheale

2

Maybe this using powershell? can also be done with ssis script c#

$Username = "MyUserName";
$Password = "MyPassword";

function Send-ToEmail([string]$email, [string]$attachmentpath){

    $message = new-object Net.Mail.MailMessage;
    $message.From = "Micheale@gmail.com";
    $message.To.Add($email);
    $message.Subject = "Monthly Report";
    $message.Body = "Greetings, Attached you will find";
    $attachment = New-Object Net.Mail.Attachment($attachmentpath);
    $message.Attachments.Add($attachment);

    $smtp = new-object Net.Mail.SmtpClient("localhost", "25");
    #$smtp.EnableSSL = $true;
    $smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
    $smtp.send($message);
    write-host "Mail Sent" ; 
    $attachment.Dispose();
 }
 
$sql = "SELECT * from boomshaka"
$Rows = Invoke-Sqlcmd -ServerInstance localhost -Database grafana -Query $sql

ForEach( $Row in $Rows)
{
   	$file = "D:\reports\" + $Row.Email + ".xml"
    $Row.cpu | Out-File -FilePath $file
	$file
    Send-ToEmail  -email $Row.Email -attachmentpath $file;
}

image
image736×247 14.8 KB

3

Hi Sir,

Excellence.
I will give a try & come back to you.
Thanks.

Regards,
Micheale

4

As a bit of a sidebar, this is one of the major reasons why I hate PowerShell. Having credentials in clear text code like this is a major security violation where I work. It should be where you work, as well.

$Username = "MyUserName";
$Password = "MyPassword";

They need to be written as "Trusted Connections" based on the current login.

1 Like
5

100% corrent @JeffModen but this is just as an example of course you would not have this in plaint text. Also this is the credentials for the email/smtp not sql server.
also this would be schedule and saved in a location where only the Admin would have access to and already knows the user name and password or it could be configured as a env var or some other sort of more secure way.

or person scheduling this, admin, would provide username and password as parameter.

powershell is not the issue, any script/app should never use the above method of clear text password

6

as @JeffModen correctly mentions the clear text password should not be used. Instead maybe you leverage $Env

$Env:smtp_user = 
$Env:smtp_password =
1 Like
7

Excellence.
It's working.
Thanks.