Sending email from java using SQL Server sp_send_dbmail

Question

I am trying to send email from java using SQL Server sp_send_dbmail

From within SQL Server itself the following works fine to send an HTML email. [i.e. I have set everything up correctly for sending emails as per Microsoft's instructions ]

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'example profile',
    @recipients = 'me@example.com',
    @body = @tableHTML,
    @body_format = 'HTML' ,
    @subject = 'Email from SQL Server';

However when trying to send from java using the following code

public static synchronized int sendEmail(String profileName,String recipients,String body, String body_format,String subject) {
        final String sendEmailStr = "{execute msdb.dbo.sp_send_dbmail (?,?,?,?,?,?)}";
        dbConn  = DBConnection.getInstance();
        Connection conn = dbConn.getConnection();
        CallableStatement stmt = null;
        int result = RESULT_FAILED;
        try {
            stmt = conn.prepareCall(sendEmailStr);
            stmt.setString("profile_name", profileName);
            stmt.setString("recipients", recipients);
            stmt.setString("body", body);
            stmt.setString("body_format", body_format);
            stmt.setString("subject", subject);

            stmt.registerOutParameter(6, java.sql.Types.INTEGER);
            stmt.execute();
            result = stmt.getInt(6);
        } catch(SQLException e) {
            System.out.println(e);
            Log.getInstance().write("Exception on sendEmail " + e.toString());
        } finally {
            try {
                stmt.close();
                dbConn.returnConnection(conn);
            } catch(SQLException e) {
                System.out.println(e);
                Log.getInstance().write("Exception on sendEmail " + e.toString());
            }
        }
        return result;
    }

I get the following exception com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter profile_name was not defined for stored procedure

What am I doing wrong?

Yogendra Singh · Accepted Answer · 2012-10-24T19:14:42.393

1

Please change your query string to use call as below:

 final String sendEmailStr "{ call msdb.dbo.sp_send_dbmail (?,?,?,?,?,?) }";

To use the stored proc from your current DB, please execute this in your current DB to wrap the MSDB call through a local procedure:

  Use [MyDatabase]; 
     CREATE PROCEDURE [MyDB].[SEND_EMAIL_DB] 
       @profile_name2 varchar(max), 
       @recipients2 varchar(max), 
       @body2 varchar(max), 
       @body_format2 varchar(max), 
       @subject2 varchar(max), 
       @p_result int OUTPUT 
     AS 
       BEGIN  
          exec msdb.dbo.sp_send_dbmail  @profile_name=@profile_name2,
                   @recipients=@recipients2,@body=@body2,
                   @body_format=@body_format2,@subject=@subject2 
       END

edited Oct 24 '12 at 19:14

answered Oct 24 '12 at 18:17

Yogendra Singh

33,927
6
63
73

I got `com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '{'.` – gordon613 Oct 24 '12 at 18:23
I then tried removing the curly brackets from sendEmailStr and I got `Exception on sendEmail com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'`. – gordon613 Oct 24 '12 at 18:24
If I use indexes, how does the computer know which parameter goes where. Granted profile_Name is the first parameter, but body is not the third parameter but the eighth in http://technet.microsoft.com/en-us/library/ms190307.aspx – gordon613 Oct 24 '12 at 18:26
@gordon613: wjere is this `@PO`. I don't see that in code. I got the index/name thing, but you were already using using index for register param. – Yogendra Singh Oct 24 '12 at 18:26
See http://stackoverflow.com/questions/7038818/ms-sql-exception-incorrect-syntax-near-p0 Seems to be the rowcountparameter according to Mitch – gordon613 Oct 24 '12 at 18:28
@gordon613: At same place, its mentioned, braces are required. Put them back and have a space in between. – Yogendra Singh Oct 24 '12 at 18:29
OK. Braces are back (with a space this time) and we're back to `com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '{'` – gordon613 Oct 24 '12 at 18:32
@gordon613 Did you add space after { and before }? – Yogendra Singh Oct 24 '12 at 18:35
I did add space after { and before } – gordon613 Oct 24 '12 at 18:37
@gordon613 I do call me stored procs this way and ther all work. Only other difference I notice is that I use `call` and I see `execute` in yours. This is a last advice. Would you mind changing that and try i.e. use `{ call msdb.dbo.sp_send_dbmail (?,?,?,?,?,?) }` ? – Yogendra Singh Oct 24 '12 at 18:39
OK. Thanks! An improvement now... `com.microsoft.sqlserver.jdbc.SQLServerException: The formal parameter "@body" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output` – gordon613 Oct 24 '12 at 18:43
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/18531/discussion-between-yogendra-singh-and-gordon613) – Yogendra Singh Oct 24 '12 at 18:44

Sending email from java using SQL Server sp_send_dbmail

1 Answers1