StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrouble turning a Simple MsSQL select into a monitor
text
Body
copied!<p>I have a SQL Statement that works as I want.</p> <pre><code>select COUNT(*), MIN(emailed_to)from email.email_archive group by emailed_to order by COUNT(*) desc</code></pre> <p>The output looks like this.</p> <p><pre><code>13 deadlockIE12388nnhy32@hepmeplease.com; 8 deadlockIE1277yhygt@hepmeplease.com; 4 deadlockFF17uyt9xx967@hepmeplease.com; ... ... ... 1 deadlockFF17uytsdfa7@hepmeplease.com;</pre></code></p> <p>This is simple enough, but then I would have to remember to run the select every day and make sure things are ok. I want to have the stored procedure email me once in a while and I can decide if I have an issue. So I have hacked together the following from many resources:</p> <p><pre><code>use MYDB; go IF SCHEMA_ID('monitors') IS NULL EXECUTE('CREATE SCHEMA monitors AUTHORIZATION dbo') GO if object_id('monitors.email_abuse') is null exec('create procedure monitors.email_abuse as print ''stub'' return'); GO alter procedure monitors.email_abuse (@to varchar(max) = 'itops@hepmeplease.com', @sendemail tinyint = 1) as set nocount on ; set transaction isolation level read uncommitted ; begin try declare @errmsg varchar(max) = '', @subject nvarchar(255); select @subject = 'Run Away Email Monitor'; select @errmsg = REPLICATE(char(10),1)+ '# of Emails'+ REPLICATE(char(9),1)+ 'Email Address'+ REPLICATE(CHAR(10),1); select @errmsg = @errmsg +REPLICATE(char(9),1)+ CAST(COUNT(*) as CHAR(10))+ REPLICATE(char(9),1)+ CAST(MIN(emailed_to) as CHAR(45)) from email.email_archive group by emailed_to order by COUNT(*) desc; print @errmsg; if @sendemail = 1 begin exec master.dbo.sp_email @to = @to, @subject = @subject, @body = @errmsg; end end try begin catch -- unexpected errors exec sp_raise_error @rethrow = 1, @textdata = N'Error in monitors.email_abuse', @emailTo = N'itops@hepmeplease.com' end catch go</pre></code></p> <p>But it then emails me the following output that is just one line. I know that there are many lines but for some reason when I put the <code>COUNT(*), MIN(emailed_to)</code> into the <code>CAST</code> statement this no longer works. I get a email that has the header and one line. If I just print the output of @errmsg I exactly what I get in the email, the header and the one line. just like below.</p> <p><pre><code># of Emails Email Address 1 y@y.com;</pre></code> </p> <p>I am not sure what I am doing wrong with my cast statement. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload