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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


12
0
Sign in
to vote
ID 361954 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 8/14/2008 7:12:06 PM
Access Restriction Public

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
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