Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Using sp_send_dbmail through PB

  1. How-to
0
Votes
Undo
  1. Louis Arena
    Louis Arena
  2. PowerBuilder
  3. Thursday, 20 December 2018 20:35 PM UTC

Why am I able to send a simple email through sp_spend_dbmail on the SQL server but when I try to send the same code through Powerbuilder it does not work.  For example, the following code works fine in ISQL

 

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQL_Mail',
@recipients= 'anyone@outlook.com',
@subject= 'Test Message',
@body='Body of email'
go

When I execute the following code in RMS, it does not work.  I get a return code of 100 which makes no sense and there really is no explanation of this error.

 

DECLARE send_dbmail PROCEDURE FOR msdb.dbo.sp_send_dbmail
@profile_name = 'SQL_Mail',
@recipients = 'anyone@outlook.com',
@subject = 'Test Message',
@body = 'Body of email'
using SQLCA
;
EXECUTE send_dbmail ;

Any help would be appreciated.

 

Louis

Create a datawindow with a bigserial field using I...
Application License Per User Software
Responses (4)
Louis Arena
Louis Arena Accepted Answer Pending Moderation
  1. Friday, 21 December 2018 20:06 PM UTC
  2. PowerBuilder
  3. # 1

Olk, so here is the "fix".

 

When you execute the SP in ISQL a result of 'mail queued' is returned.  In my example in PB I am not coding for any return results so SQL server returns a 100.  My code after the exec command checks for errors.  I was using the following.

IF sqlca.sqlcode <> 0 THEN
    ls_error = sqlca.sqlerrtext
    ls_error = 'Could not send the email/text message, error: ' + ls_error
    ROLLBACK;
    MessageBox ("Email/Text", ls_error, Exclamation!)
    RETURN
END IF
commit;

So when SQL server sent me back code 100 I rolled back.  But code 100 is really not a return code to rollback.  So when I changed to just this

IF sqlca.sqlcode < 0 THEN

....

End if

and then executed a commit; it worked.

 

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 21 December 2018 21:13 PM UTC
Thanks Louis for letting use know how it was solved!

That is excellent news! :-)
  1. Helpful
There are no comments made yet.
Louis Arena
Louis Arena Accepted Answer Pending Moderation
  1. Thursday, 20 December 2018 22:32 PM UTC
  2. PowerBuilder
  3. # 2

Also there is not text in the SQLCA.SQLErrText variable.

Comment
There are no comments made yet.
Louis Arena
Louis Arena Accepted Answer Pending Moderation
  1. Thursday, 20 December 2018 21:33 PM UTC
  2. PowerBuilder
  3. # 3

True, however, the email is never sent.  So something is not happening. 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 20 December 2018 20:43 PM UTC
  2. PowerBuilder
  3. # 4

Hi Louis;

   A +100 return code is not an error. It just means that the SP has not returned any ANSI result set or you have reached the end of the RS during a Fetch operation. The real error (if any) would be reflected in the SQLCA.SQLDBCode and SQLCA.SQLErrText variable values.

Regards ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.