Hello -
I'm having a problem sending the query set as an email text attachment. Test transmissions from Database mail working fine.
Send simple messages with the sp_send_dbmail sproc works fine as well.
It is only when I try and send a query result that things blow up. The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.
Surface config features have database mail on, and SQL Mail off.
Anyone know the solution to this?
Thanks in advance!
Actual SQL ...
use epic
go
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQLMail',
@.recipients = 'rvolters@.whaleyfoodservice.com',
@.body = 'Testing sqlmail with sproc.',
@.subject = 'Orders with invalid tech numbers',
@.query = 'SELECT order_no, order_ext, line_no from epic..STG_BNERPT_INVALIDTECHNOS' ,
@.attach_query_result_as_file = 1 ;
Message Results...
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Error initializing COM
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Investigating the sproc itself shows...
Line 476 in the sproc is the beginning of a 'trap'
--Raise an error it the query execution fails
-- This will only be the case when @.append_query_error is set to 0 (false)
IF( (@.RetErrorMsg IS NOT NULL) AND (@.exclude_query_output=0) )
BEGIN
RAISERROR(14661, -1, -1, @.RetErrorMsg)
END
RETURN (@.rc)
this is the last section of code in the sproc
I have a very similar issue.
When i execute the following everything work 100%.
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'DBA Mail',
@.recipients = me@.whatever.co.za',
@.body = 'Test email from server using dbmail',
@.subject = 'Test email from server using dbmail',
@.query = 'SELECT TOP 1 * FROM TABLE A Order by AlertDTTM DESC' ;
As soon as i put this exact code in a trigger i get the following error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106
Anyone ?
|||We have same problem for some time. it has been fixed for us after I made some changes to the security setting and rebooted the server..
We are running SQL 2005 on Windows 2003 SP1. Check the following link
http://support.microsoft.com/default.aspx/kb/926642/en-us
Faeed
