How can I determine the error code from sp_send_dbmail in SQL Server 2005?

I'm trying to send an attachment using sp_send_dbmail in SQL Server 2005. I first wrote a CLR stored procedure that saves some content to a file on the server, then calls the stored procedure listed below, and then deletes the file it created. I call this CLR stored procedure from my ASP.NET 2010 application. Locally, this functionality works, both from SQL Management Studio and from within my application.

Here's the essential stuff from my CLR sproc:

public static void SendExportAttachment(string email, string fileContents, string mailProfile, string temporaryFileName)
{
    // First save the file to the file system
    StreamWriter sw = new StreamWriter(temporaryFileName);
    sw.Write(fileContents);
    sw.Close();
    sw = null;

    // Execute the SendExportResultEmail (see below)
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SendExportResultEmail", connection);
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@ProfileName", mailProfile));
        command.Parameters.Add(new SqlParameter("@Recipients", email));
        command.Parameters.Add(new SqlParameter("@TemporaryFileName", temporaryFileName));
        command.ExecuteNonQuery();
    }

    // Remove the file from the file system
    File.Delete(temporaryFileName);
}

Here's the stored procedure that sends the email:

CREATE PROCEDURE [dbo].[SendExportResultEmail]
     @ProfileName NVARCHAR(50)
    ,@Recipients NVARCHAR(100)
    ,@TemporaryFileName NVARCHAR(2000)
AS

SET NOCOUNT ON

DECLARE @ErrorID INT
DECLARE @RtnCode INT
DECLARE @Body VARCHAR(8000)
DECLARE @Subject VARCHAR(1000)
DECLARE @mailitem_id INT

SET @Body = 'Some Body'
SET @Subject = 'Some title'

EXEC @RtnCode = msdb.dbo.sp_send_dbmail
     @profile_name = @ProfileName
    ,@recipients   = @Recipients
    ,@body         = @Body
    ,@subject      = @Subject
    ,@body_format = 'TEXT'
    ,@file_attachments = @TemporaryFileName
    ,@mailitem_id = @mailitem_id OUTPUT

SET @ErrorID = @@ERROR

IF @RtnCode <> 0 BEGIN
    SELECT @ErrorID
END

After successfully testing this code as part of my application locally, I moved it to our test server. On the test server, it sends the mail successfully when I execute the CLR sproc from Management Studio. However, when I execute it from my ASP.NET application, my return code from msdb.dbo.sp_send_dbmail is 1, and @@ERROR is 0 - I get no other errors. I know that in SQL 2008 I would probably get a more helpf开发者_JAVA技巧ul @@ERROR code, at least according to the 2008 documentation.

Does anybody have any suggestions? What do you think I might be doing wrong?

Thanks very much in advance, Jim

since you are running msdb.dbo.sp_send_dbmail, which will catch any error related to the e-mail, all error info comes from via the return value: @RtnCode. Only a malformed call to msdb.dbo.sp_send_dbmail will result in any @@ERROR value. Why not move to BEGIN TRY - BEGIN CATCH used in SQL Server 2005+?

• when I run your code with a bad @ProfileName (my first thought), I get @RtnCode=4

• when I run your code with a bad @TemporaryFileName, I get @RtnCode=1, which is what you are reporting. Have you checked that the file path and name are valid and accessible from the SQL Server?