title: How can I determine the error code from sp_send_dbmail in SQL Server 2005?
body: <p>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.</p>
  
  <p>Here's the essential stuff from my CLR sproc:</p>
  
  <pre><code>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);
  }
  </code></pre>
  
  <p>Here's the stored procedure that sends the email:</p>
  
  <pre><code>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 &lt;&gt; 0 BEGIN
      SELECT @ErrorID
  END
  </code></pre>
  
  <p>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 helpful @@ERROR code, at least according to the 2008 documentation.  </p>
  
  <p>Does anybody have any suggestions?  What do you think I might be doing wrong?</p>
  
  <p>Thanks very much in advance, Jim</p>