xp_SendMail & Char(13)

6 Jan 2006 9:02 PM

LCooker

I'm calling a stored procedure from Visual Basic 6.0 which uses
xp_SendMail. The @message parameter was receiving a string variable
which was built using vbcrlf constants to line feed the message.
xp_SendMail would fail everytime I sent this string. If I removed the
line feeds, the email was sent.

To solve this problem, I created a local table and inserted my message
with the line feeds using char(13) + char(10). Then I take the message
directly from this table, put it in a varchar and pass that to
@message. When I test this code from QA, I receive the email. When I
call the stored procedure from VB, no email. I've run a trace and
there are no errors in the stored procedure. It looks as if the
message was sent. What gives?

Here is some of my code:

--Call to stored procedure from VB
cnSQL.Execute ("SendSQLMail '" & strEmailAddresses & '", '" &
strMsgSubject _
    & "', '" & strMsgBody & "'")

--SendSQLMail stored procedure
if exists (select * from sysobjects where id =
    object_id('dbo.SendSQLMail') and sysstat & 0xf = 4)
    drop procedure dbo.SendSQLMail
GO
CREATE PROCEDURE dbo.SendSQLMail
    @EmailAddresses   VARCHAR(8000)
    , @MsgSubject    VARCHAR(8000)
    , @MsgBody        VARCHAR(8000)

AS
    /* DECLARATION BLOCK */
    DECLARE    @SUCCESS                    INT
    DECLARE @FAILURE                    INT
    DECLARE    @RAIS_PARAMETER_NOT_NULL    INT
    DECLARE    @RAIS_STATE                    INT
    DECLARE    @RAIS_SEVERITY                INT

           /* ASSIGNATION block */
    SELECT    @SUCCESS = 1
    SELECT    @FAILURE = 0
    SELECT    @RAIS_PARAMETER_NOT_NULL = 100100
    SELECT    @RAIS_SEVERITY = 15
    SELECT    @RAIS_STATE = 1

    /*REQUIRED PARAMETER VALIDATION */
    IF @EmailAddressesID IS NULL
        OR @MsgSubject IS NULL
        OR @MsgBody IS NULL

        BEGIN
        RAISERROR (@RAIS_PARAMETER_NOT_NULL, @RAIS_SEVERITY, @RAIS_STATE)
        RETURN @FAILURE
        END

    /* MAIN PROCESSING */
    -- Send the msg
    EXEC MASTER..xp_sendmail @Recipients = @EmailAddresses, @Subject =
@MsgSubject, @message = @MsgBody

GO
    Grant Exec on dbo.SendSQLMail to Public
GO

7 Jan 2006 8:49 PM

ML

Try it without char(10). That's: char(13) only.

ML

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