November 8, 2007 at 8:47 am
I have various scripts running overnight as jobs that check the integrity of data and send various emails out to people if any weird data creeps in there. I've had these things running for years. I added a new one to a job recently and it just won't fire off the email, although it works fine in Query Analyzer. Also, other scripts that work the same way are fine.
For instance, this script checks for the existence of records in table xxtstats and if there are any, it sends an email:
-- Count number of differences
IF (SELECT COUNT(*) FROM xxtstats WHERE xts_value_diff <> 0 OR xts_count_diff <> 0) <> 0
BEGIN
-- Send list of differences by email to administrator
USE master
EXEC xp_startmail
EXEC xp_sendmail @recipients = 'itsupport@mycompany.co.uk',
@query = 'USE STXbaxt SELECT xts_datetime, xts_table, xts_field, xts_value_diff, xts_count_diff FROM xxtstats WHERE xts_value_diff <> 0 OR xts_count_diff <> 0',
@subject = 'Data validation checks on the data-warehouse have found some anomalies',
@message = 'The attached file contains a list of tables that have failed the data validation checks.',
@attach_results = 'TRUE', @width = 250
EXEC xp_stopmail
END
This works fine in Query Analyzer AND running as a SQL Agent job.
This script does the same thing, except with different tables:
-- Count number of differences
IF (SELECT COUNT(*) FROM tmp_stmove) <> 0
BEGIN
-- Send list of differences by email to administrator
USE master
EXEC xp_startmail
EXEC xp_sendmail @recipients = 'itsupport@mycompany.co.uk',
@query = 'USE STXbaxt SELECT * FROM tmp_stmove ORDER BY [DATE], [TIME]',
@subject = 'Lean lift adjustment transactions not performed by Stores staff',
@message = 'The attached file contains a list of adjustment transactions that have not been performed.',
@attach_results = 'TRUE', @width = 250
EXEC xp_stopmail
END
And it runs fine in Query Analyzer, but does not send the email when run as a SQL Agent job.
I've got loads of these and similar scripts running on this server....this is the only one that won't. I'm baffled!
Any inspired ideas out there?
November 8, 2007 at 9:09 am
Well, I've kind of found my own solution to this problem........
If I remove the "ORDER BY [DATE], [TIME]" section of the SQL statement that is embedded in the xp_sendmail script, it works fine.
Yet, the whole thing, including the ORDER BY bit, works fine in Query Analyzer.
Is this a bug or a "feature"?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply