Hi All,

I am using the new [sp_send_cdosysmail] from MS
(http://support.microsoft.com/kb/312839/en-us).  I need to add an attachment,
so I added the old fix...
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @Attachment
(in this case @Attachment = 'C:\Text.txt')

As well, I added the error catching from the previous steps to follow the SP
call.
It is erroring out with the usual cryptic error:
"Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should
be discarded."

However the...
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

gives me:
@source= ODSOLE Extended Procedure
@description= Output values of type Object are not allowed in result sets.

I've looked all over the place, but I can't find a fix, or even a reference
to this problem.  Can someone point me in the right direction?

I am using SQL 2000 SP4 on Win 2003 Ent SP1.  I don't know what "Version" of
CDO from the proper sources, but I have found the cdosys.dll and it has file
version 6.5.6756.0, and product version 6.5.

Please help me...

Thanks,
Ryan
SQL DBA

Please post the entire script/procedure.

Also - why are you specifying null in the place of the sp_OAMethod
returnvalue parameter? This could be the problem. Try using a variable
instead.


ML

---
http://milambda.blogspot.com/

The reason, I am using a null return value, is that is what is recommended in
the MS Script.  As well, I discovered that if I ignore the error, and comment
out the "goto send_cdosysmail_cleanup", it works, despite the fact it returns
the error.

So, I guess my new question is how to suppress the error, or make it go away
now.

My script is just a slight tweak of what is in the link I provided, however,
the entire script is below...

Thanks in advance,
Ryan

Show quoteHide quote    ALTER PROCEDURE [dbo].[sp_send_cdosysmail]
       @From varchar(100) ,
       @To varchar(100) ,
       @Subject varchar(100)=" ",
       @Body varchar(4000) =" ",
        @CC varchar(100) = NULL,
        @BCC varchar(100) = NULL,
        @Attachment varchar(200) = null
    /*********************************************************************

    This stored procedure takes the parameters and sends an e-mail.
    All the mail configurations are hard-coded in the stored procedure.
    Comments are added to the stored procedure where necessary.
    References to the CDOSYS objects are at the following MSDN Web site:     http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

    ***********************************************************************/
       AS
       Declare @iMsg int
       Declare @hr int
       Declare @source varchar(255)
       Declare @description varchar(500)
       Declare @output varchar(1000)

    --************* Create the CDO.Message Object ************************
       EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OACreate')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES
(getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
@description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
                   RETURN
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               RETURN
             END
         END

    --***************Configuring the Message Object ******************
    -- This is to configure a remote SMTP server.
    --
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
       EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty sendusing')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES
(getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source,
@description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END
    -- This is to configure the Server Name or IP address.
    -- Replace MailServerName by the name or IP of your SMTP Server.
       EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.ddrs.net'
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty smtpserver')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END

    -- Save the configurations to the message object.
       EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty Update')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty Update')                
           GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END

    -- Set the e-mail parameters.
       EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty To')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty To')                
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END

        if @CC != ''
        begin
            EXEC @hr = sp_OASetProperty @iMsg, 'CC', @CC
           IF @hr <>0
            BEGIN
               SELECT @hr
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty CC')
               EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
               IF @hr = 0
                BEGIN
                   SELECT @output = '  Source: ' + @source
                   PRINT  @output
                   SELECT @output = '  Description: ' + @description
                   PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty CC')                
                       GOTO send_cdosysmail_cleanup
                END
               ELSE
                BEGIN
                   PRINT '  sp_OAGetErrorInfo failed.'
                   GOTO send_cdosysmail_cleanup
                END
            END
        end

        if @BCC != ''
        Begin
            EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC
           IF @hr <>0
            BEGIN
               SELECT @hr
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty BCC')
               EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
               IF @hr = 0
                BEGIN
                   SELECT @output = '  Source: ' + @source
                   PRINT  @output
                   SELECT @output = '  Description: ' + @description
                   PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty BCC')                
                       GOTO send_cdosysmail_cleanup
                END
               ELSE
                BEGIN
                   PRINT '  sp_OAGetErrorInfo failed.'
                   GOTO send_cdosysmail_cleanup
                END
            END
        End

       EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty From')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty From')                
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END



       EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty Subject')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty Subject')
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END

    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
       EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OASetProperty TextBody')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END

    if @Attachment != ''   
    Begin
        EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @Attachment
        IF @hr <>0
            BEGIN
               SELECT @hr
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OAMethod AddAttachment')
               EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
               IF @hr = 0
                BEGIN
                   SELECT @output = '  Source: ' + @source
                   PRINT  @output
                   SELECT @output = '  Description: ' + @description
                   PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OAMethod AddAttachment')
            --           GOTO send_cdosysmail_cleanup
                END
               ELSE
                BEGIN
                   PRINT '  sp_OAGetErrorInfo failed.'
                   GOTO send_cdosysmail_cleanup
                END
            END

    End

    EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
       IF @hr <>0
         BEGIN
           SELECT @hr
           INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'Failed at sp_OAMethod Send')
           EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
           IF @hr = 0
             BEGIN
               SELECT @output = '  Source: ' + @source
               PRINT  @output
               SELECT @output = '  Description: ' + @description
               PRINT  @output
               INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OAMethod Send')
                   GOTO send_cdosysmail_cleanup
             END
           ELSE
             BEGIN
               PRINT '  sp_OAGetErrorInfo failed.'
               GOTO send_cdosysmail_cleanup
             END
         END


    -- Do some error handling after each step if you have to.
    -- Clean up the objects created.
        send_cdosysmail_cleanup:
    If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
    BEGIN
        EXEC @hr=sp_OADestroy @iMsg

        -- handle the failure of the destroy if needed
        IF @hr <>0
             BEGIN
            select @hr
                    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(),
@@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description,
@output, 'Failed at sp_OADestroy')
                   EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

            -- if sp_OAGetErrorInfo was successful, print errors
            IF @hr = 0
            BEGIN
                SELECT @output = '  Source: ' + @source
                    PRINT  @output
                    SELECT @output = '  Description: ' + @description
                    PRINT  @output
                INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid,
@From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output,
'sp_OAGetErrorInfo for sp_OADestroy')
            END

            -- else sp_OAGetErrorInfo failed
            ELSE
            BEGIN
                PRINT '  sp_OAGetErrorInfo failed.'
                    RETURN
            END
        END
    END
    ELSE
    BEGIN
        PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From,
@To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is
NULL, sp_OADestroy skipped')
            RETURN
    END

So, have you tried what I've suggested?


ML

---
http://milambda.blogspot.com/

Capturing the output as an Int suppresses the error, yes.

It's still a whacked out response.

Thanks for the help.
Ryan

Thanks,

Show quoteHide quote

Obviosuly it's due to compatibility issues - sp_OAMethod expects output from
the ActiveX method, but not all of the latter have an output. It's best in
such cases to simply use a dummy variable to "catch" what never gets
returned. :)

BTW: have you considered using another procedure to send mail from SQL?
(e.g.
http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html)


ML

---
http://milambda.blogspot.com/

You're talking about xp_SMTP_sendMail right?  I considered it, but it would
be a big hassle, and more time than I want to take, to get a "third Party"
dll approved by the management on all of our 2000 SQL Servers, especially
since the sp_send_dbmail has had no significant problems so far that I have
found, so this is just a patch, until we move it all to 2005.

Ryan

Show quoteHide quote

Oh, I see. I wish you a successful *move upwards* ASAP.


ML

---
http://milambda.blogspot.com/

Returned first row of a column in a group by
Sub Query Problem
LEFT JOIN... WHERE vs AND ?
Optimizing this crazy setup...
how to pass in a long string to stored procedure