Question sp_send_dbmail - Return value

[grasshopper]

Hello All,

Below is a code C# example of sending an SQL DB email using sp_send_dbmail. Works great. However, the stored procedure is suppose to return an integer to denote if it was successful or failed.

My problem is getting that integer value. The info I have found says it returns an "integer value" but not as an OUTPUT variable. So I am lost. I have try ExecuteScalar and ExecuteQuery (@@Error, @Return, etc). As mentioned the email gets sent... I simply want to get the results code back to the C# code.

Probably something real easy... that I am not aware of.

Would anyone be so kind as to help me complete this code and get the return value from the SP run?

Thank you,

Eric

            using (SqlConnection PPPautoConnection = new SqlConnection(stringConn))
            {
                Database db = ABCSqlDataHelper.CreateConnection(stringConn);
                DbCommand dbCommand = db.GetStoredProcCommand("msdb.dbo.sp_send_dbmail");
                dbCommand.CommandTimeout = 1200;
                db.AddInParameter(dbCommand, "@profile_name", Profile_Name);
                db.AddInParameter(dbCommand, "@recipients", DbType.String, EmailTo);
                db.AddInParameter(dbCommand, "@from_address", DbType.String, EmailFrom);
                db.AddInParameter(dbCommand, "@subject", DbType.String, EmailSubject);
                db.AddInParameter(dbCommand, "@body", DbType.String, EmailBody);
                db.AddInParameter(dbCommand, "@body_format", Body_Format);
                db.ExecuteNonQuery(dbCommand);
                //var vResults = db.ExecuteScalar(dbCommand);
                results = Convert.ToInt32(db.GetParameterValue(dbCommand, "@@ERROR"));
            }

Errors on

results = Convert.ToInt32(db.GetParameterValue(dbCommand, "@@ERROR"));

 

[jmcilhinney]

The command only has whatever parameters you added to it. Did you add a parameter named "@@ERROR"? If not then it doesn't have one. You are supposed to add a parameter to the command for each parameter the sproc being called has, setting their Direction appropriately if they are not just input parameters. ReturnValue is one of the possible directions.

By the way, I haven't checked but I would assume that @@ERROR is a global variable rather than a sproc parameter.

 

[grasshopper]

My bad... I was not clear... The sp_send_dbmail is a system stored procedure in the system database / msdb.dbo section of the server.

I have only been given execute rights and when I look at the parameters the last parameter is "Returns Integer". That is all I know at this time.

I was hoping that someone else uses the sp_send_dbmail sp and had a solution.

Thanks,

Eric