June 12, 2014 at 10:39 am
Hi,
I have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. Can someone correct my condition check part of the code? Thank you in advance!!!
use msdb
go
create table ##resultset (
total int)
insert into ##resultset (total)
Select Count(*) from table
declare @total int
select total from ##resultset
if (@total) >= 20
--send email to the required recipents
USE msdb
EXEC sp_send_dbmail
@profile_name = 'profile',
@recipients = 'emal address',
@subject = 'Current Count of Docs',
@body = 'The current count of docs',
@execute_query_database = 'msdb',
@query = 'select total from ##resultset'
drop table ##resultset
June 12, 2014 at 10:45 am
mickeytech2000 (6/12/2014)
Hi,I have the following code to send out an email if the row count of a table exceeds a certain number. But it seems to fire an email alert no matter what. Can someone correct my condition check part of the code? Thank you in advance!!!
use msdb
go
create table ##resultset (
total int)
insert into ##resultset (total)
Select Count(*) from table
declare @total int
select total from ##resultset
if (@total) >= 20
--send email to the required recipents
USE msdb
EXEC sp_send_dbmail
@profile_name = 'profile',
@recipients = 'emal address',
@subject = 'Current Count of Docs',
@body = 'The current count of docs',
@execute_query_database = 'msdb',
@query = 'select total from ##resultset'
drop table ##resultset
It is because you have an if statement followed by two statements with no begin/end blocks. Not really sure why you are using a global temp table here and the USE msdb is not needed.
if (select COUNT(*) from table) >= 20
--send email to the required recipents
EXEC sp_send_dbmail
@profile_name = 'profile',
@recipients = 'emal address',
@subject = 'Current Count of Docs',
@body = 'The current count of docs',
@execute_query_database = 'msdb',
@query = 'select count(*) from table'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2014 at 10:58 am
Now that I added BEGIN ...END after the if statement, it is not sending any email at all. Is the @total correctly coded?
Thank you for your help!!!
June 12, 2014 at 12:41 pm
mickeytech2000 (6/12/2014)
Now that I added BEGIN ...END after the if statement, it is not sending any email at all. Is the @total correctly coded?Thank you for your help!!!
I don't know. What is your code? I would suggest using the code I posted as your ENTIRE code. There really is no need to declare a variable, populate with an aggregate function to just evaluate the results one time. Maybe your actual code is using the variable again?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2014 at 3:16 pm
That worked:-) Thank you!!!
June 12, 2014 at 3:20 pm
mickeytech2000 (6/12/2014)
That worked:-) Thank you!!!
Glad that worked for. Thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply