none
sp_send_dbmail fails when @query parameter and OLE Automation

    Question

  • Hi,

    I'm using OLE Automation to build disk drive information into a table, and then using sp_send_dbmail with the @query to do a select from the table and include the information in an email. 

    Both parts run fine on their own, but when I run them together, without a GO between them, then I get the following failure:

    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 504
    Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106

     

    Previous forum discussion has suggested doing the 2 parts in different threads as a workaround, so I have created seperate stored procedures for each part.  But still no joy.  When I run without a GO it still gives the error.

    For example,

    exec [dbo].[sp_get_diskspace]

    exec [dbo].[sp_dbmail_send_dm]

    fails.... but:

    exec [dbo].[sp_get_diskspace]

    go

    exec [dbo].[sp_dbmail_send_dm]

    works...

    Since I want to run the above from a stored procedure I can't have the GO in that position.

    Any workaround suggestions?

    ------------
    Here's an extract from the procs (procs were taken from other forum appends):

    proc 1:

    -------------------------------------------------------
    EXEC master.dbo.xp_fixeddrives

    EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

    -- ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/e8982e05-4984-4406-9760-285e8c028ddf.htm

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    DECLARE dcur CURSOR LOCAL FAST_FORWARD
    FOR SELECT drive from [drives_diskspace]
    ORDER by drive
    OPEN dcur
    FETCH NEXT FROM dcur INTO @drive
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

    UPDATE [drives_diskspace]
    SET TotalSize=@TotalSize/@MB, ServerName = CONVERT(char(20), SERVERPROPERTY('servername')),
    FreespaceTimestamp = (GETDATE())
    WHERE drive=@drive

    FETCH NEXT FROM dcur INTO @drive
    END
    CLOSE dcur
    DEALLOCATE dcur

    EXEC @hr=sp_OADestroy @fso

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    -------------------------------------------------------

    proc 2:

    -------------------------------------------------------
    EXEC [msdb].[dbo].[sp_send_dbmail]
    @profile_name = 'DBMail',
    @recipients = @myrecipients ,
    @subject = @mysubject ,
    @body = @mybody,
    @query = @myquery
    -------------------------------------------------------

    Any ideas appreciated.

    Regards,

    Dave.

     

    Wednesday, May 18, 2011 2:19 PM

Answers

All replies