This one is bugging the daylights out of me. We've got a stored proc that does some error checking then sends an email if there is an error. I can get it to work as long as I don't use the @query switch for sp_send_dbmail. As soon as I uncomment that, the code fails. I've Googled the error and tried every switch listed in articles about similar errors (different numbers), but still can't get the code to work. If I set the @exclude_query_output to 1, nothing happens. It just says "commands completed".
The actual sp_send_dbmail code is cribbed off of other procs that work in SQL 2012 (We haven't seen any errors in SQL 2019 either), so I know this SHOULD work which means I'm missing something obvious. Here's the scrubbed version of my code. Thoughts?
Msg 22050, Level 16, State 1, Line 2
Failed to initialize sqlcmd library with error number -2147467259.
DECLARE @Emails VARCHAR(50) = 'MyEmail@Email.com';
DECLARE @profile VARCHAR(50),
@emailTo VARCHAR(255) = @emails,
@ErrorCnt INT,
@EmailSubject VARCHAR(80),
@EmailCopy VARCHAR(255),
@EmailMsg VARCHAR(8000),
@MySQL VARCHAR(2000),
@filename VARCHAR(100)='AProcess_' + LEFT(CONVERT(VARCHAR(20),GETDATE(),112),8) + '.txt';
IF (@emailTo IS NULL)
BEGIN
SET @emailTo='OtherEmail@Email.com;';
END;
SELECT @profile = MyDB.dbo.fnGetEmailProfile();
SELECT @ErrorCnt = (SELECT COUNT(*) AS 'RecordCount'
FROM dbo.MyTable
WHERE MissedResponse = 1 OR ZeroDollars = 1);
IF @ErrorCnt > 0
BEGIN
SELECT @EmailMsg = 'This is an automated message to inform you that an error has occurred in the A process. ' +
'Please see attached error log to verify which Items have problems.';
SELECT @EmailSubject = 'ERRORS: Process A';
SELECT @MySQL = 'SET NOCOUNT ON;
SELECT ItemNumber, ItemName,
CASE WHEN ZeroDollars = 1 THEN ''Item has zero dollars. Please investigate issue.''
WHEN MissedResponse = 1 THEN ''Item is missing from file. Please investigate issue.''
ELSE ''Unknown Error. Reach out to systems analyist contact for research.'' END AS ErrorResponse
FROM dbo.MyTable
WHERE MissedResponse = 1 OR ZeroDollars = 1;';
--SELECT @MySQL;
--SELECT @EmailMsg;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @emailTo,
@query_result_header = 1,
@query = @MySQL,
@subject = @EmailSubject,
@importance = 'High',
@body = @EmailMsg;--,
--@exclude_query_output = 1,
--@attach_query_result_as_file = 1,
--@query_result_header = 1--,
--@query_attachment_filename = @filename;
END
Oh, FOR HEAVEN'S SAKE!
It was executing the query under the context of msdb, not the original database, so the query needs to have the MyDB prepended to the dbo.MyTable.
<headdesk>
I used to know that. Not sure why I ever forgot it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply