OK... I figured it out... and it was ridiculously simple.
As I mentioned, the query that is being run is pretty straight-forward and simple. Here's a slightly modified version (to protect super-secret company info
declare @BodyText nvarchar(max)
SET @BodyText = 'Attached is the quarterly audit.
@profile_name = 'SQL Mail',
SELECT SUBSTRING(description, 1, 30) as Name,
WHEN privilege = ''1''
WHEN privilege = ''0''
THEN (''System User'')
END AS ''Role''
WHERE enabled = ''1''
AND item_type = ''7765''
ORDER BY Role'
,@attach_query_result_as_file = 1
,@query_attachment_filename = 'User_Accts.txt'
,@exclude_query_output = 1;
So, the key here is the 'USE Product_Database
' line. In the Job Step Properties page, I was leaving the "Database:" option as "master", which is what it defaults to. I figured that was fine, since the code explicitly states to use the necessary database.
On all the other servers, this worked without any problems. On this
particular server, though, I had to switch the "Database:" option to Product_Database, even though it seems a bit redundant since the code does so as well.
Regardless, it worked like a charm. My guess is that it's some esoteric permission issue of some type, and since I call sp_send_dbmail before
I switch to the Product_Database, that's what is causing the problem.
Anyhow, thanks for the help, everyone.