Home Dashboard Directory Help
Search

sp_send_dbmail fails when @query parameter and OLE Automation is used in same batch by Dan Guzman


Status: 

Closed
 as Won't Fix Help for as Won't Fix


12
0
Sign in
to vote
Type: Bug
ID: 361954
Opened: 8/14/2008 7:12:06 PM
Access Restriction: Public
5
Workaround(s)
view
5
User(s) can reproduce this bug

Description

Stored procedure sp_send_dbmail fails when the @query parameter is specified and the same batch previously invoked OLE automation procs. The error messages are:

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

Note that the repro script executes as expected if a GO batch terminator is specified after the sp_OADestroy. However, this workaround is not an option in stored procedure code.

Thanks,

Dan Guzman, SQL Server MVP
Details
Sign in to post a comment.
Posted by RobertoNh on 12/28/2012 at 1:09 PM
Why can't you fix this?
Posted by Microsoft on 8/27/2009 at 1:32 PM
This bug was reopened accidently - I'm closing it again. Including original message that was sent when it was closed:

Dan,

We investigated this and are not fixing it. I've closed this bug. Here's the details from the investigation:



After investigation we decided we couldn’t produce a QFE from engine side. The design of sp_OA procs doesn't include a way to uninitialize COM before the batch ends. Fixing this in engine would require adding a new stored proc, and it'll be a partial fix since we need to deal with more problems:
. We need to change the behavior of sp_OACreate after sp_OAStop (current we return an error) to make it work for applications that use sp_OACreate after sp_db_sendmail.
. If the application keeps an object across sp_db_sendmail it's not helped by the change.
Besides these, changing COM thread model during a batch was something never cover before so it’s risky and easy to introduce regressions.

So calling sp_db_sendmail on a different thread is the only clean way to make it work. Since the dbmail code couldn’t be changed to do that, we're not fixing this item.
Posted by Alin Selicean on 10/21/2008 at 1:35 AM
Dan,

I also posted a validation, although I got a bit different error message (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 495
Query execution failed: Msg 15404, Level 16, State 19, Server SQL01, Line 1
Could not obtain information about Windows NT group/user 'DOMAIN\UserName', error code 0x5.)

Is there any chance of having this fixed ? Any news on this from MS ?
Posted by Microsoft on 9/17/2008 at 1:34 PM
Dan,

We investigated this and are not fixing it. I've closed this bug. Here's the details from the investigation:



After investigation we decided we couldn’t produce a QFE from engine side. The design of sp_OA procs doesn't include a way to uninitialize COM before the batch ends. Fixing this in engine would require adding a new stored proc, and it'll be a partial fix since we need to deal with more problems:
. We need to change the behavior of sp_OACreate after sp_OAStop (current we return an error) to make it work for applications that use sp_OACreate after sp_db_sendmail.
. If the application keeps an object across sp_db_sendmail it's not helped by the change.
Besides these, changing COM thread model during a batch was something never cover before so it’s risky and easy to introduce regressions.

So calling sp_db_sendmail on a different thread is the only clean way to make it work. Since the dbmail code couldn’t be changed to do that, we're not fixing this item.
Posted by Microsoft on 8/22/2008 at 12:10 PM
Dan,

Thanks for the report. We'll investigate and get back to you.

-Richard Waymire
Sign in to post a workaround.
Posted by RobertoNh on 12/28/2012 at 1:09 PM
Why can't you fix this? I
Posted by davehants on 6/16/2011 at 6:38 AM
I have two stored procs - the first to get the disk space values and populate my table, the second to create an email and send it using dbmail.... I have put each proc into seperate steps, in a job that is scheduled to run every day at 7am....

Rgds, Dave.
Posted by SriMagSQL on 11/16/2010 at 7:47 AM
Once I added the domain account used to calll the SP (calling sp_send_dbmail) to the Server as a login, the issue is fixed. This was suggested by a user "blake colson-405790" in the following post.

http://www.sqlservercentral.com/Forums/Topic682721-146-1.aspx
Posted by Dan Guzman on 8/14/2008 at 7:17 PM
Instead of executing sp_send_dbmail direcly, wrap sp_send_dbmail in a Service Broker activated stored procedure and invoke indirectly via a SB dialog.
Posted by Dan Guzman on 8/14/2008 at 7:16 PM
Execute sp_send_dbmail in a different batch than the sp_OA* procs.