Question sp_send_dbmail - Return value

grasshopper

New member
Joined
May 9, 2014
Messages
2
Programming Experience
10+
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"));
 
Last edited by a moderator:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,209
Location
Sydney, Australia
Programming Experience
10+
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

New member
Joined
May 9, 2014
Messages
2
Programming Experience
10+
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
 
Top Bottom