SQL Server Agent and powershell scripting - tough requirement

Question

SQL Server Agent and powershell scripting - tough requirement

mar.ko

Default port

Points: 1451
More actions
October 14, 2015 at 7:32 am

#321907

I was blown away by this blog article...
[/url]
One of the user requirements is to have key statistics that are stored in an SQL Server table placed on the subject line of the outgoing email.
Is this possible with SQL Server Agent ?
Would I use Powershell scripting to do this ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The subject line is the subject line - why would you want to put information like that in it? If you really want to, you can, but your requirement is so vague as to give us nothing to work with. Please will you show what's in your table and what you want your e-mail to look like?

John

mar.ko Default port Points: 1451 More actions · Answer 2

TO: user1;user2;user3

FROM: admin@xyz.com

SUBJECT: Hourly Report For 9 am : CORE 13 (+2) UPSELL 5 (-1)

BODY: embedded reports in HTML format - cell phone friendly

Note: There is nothing stupid about this requirement.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i've done exactly that with plain old FOR XML and sp_send_dbmail; why do you need to complicate it with powershell?

here's an old snippet:

Declare @HTMLBody varchar(max),

@TableHead varchar(max),

@TableTail varchar(max),

@EmailGroup varchar(max) = 'mar.ko <marko@somedomain.com>;Lowell <lowell@anotherdomain.com>;',

@MySubject varchar(4000)

Set NoCount On;

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=0 cellspacing=0 border=0>' +

'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

'<td align=center><b>Product</b></td>' +

'<td align=center><b>Provider</b></td>' +

'<td align=center><b>Data Source</b></td>' +

'<td align=center><b>Is Linked?</b></td></tr>';

Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

name As

,

product As

,

provider As

,

data_source As

,

is_linked As

From sys.servers

Order By is_linked, name

For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers

Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))

Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')

Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

SELECT @MySubject = ' Total Records: ' + convert(varchar,COUNT(*)) FROM From sys.servers

-- return output for revuew

Select @HTMLBody

SELECT @MySubject

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'My DB mail Profile',

@recipients = @EmailGroup,

@subject = @MySubject,

@body = @HTMLBody,

@body_format = 'HTML';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mar.ko Default port Points: 1451 More actions · Answer 4

Wow, thanks for that Lowell....nicely done !

Now I need to look into:

1) establishing and configuring my mail profile

2) scheduling the report via SQL Server Agent.