Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

BI Procedure Email Notifications

I have a BI procedure that performs actions, generates a log, and then tries to email the log.  I have attempted to use the Send Email Task, but it has limitations.  There is no way to specify the logon parameters for the server (which is a pretty dumb limitation).  So, this task will not work, since my server requires authentication.

I have tried using the Notify Operator task.  This works (sends a message), however I do not see a way to make the message text come from a variable.  I tried setting Expressions but the editor won't accept any syntax when I try and set message=variable.

Is there a way to send an email from a BI procedure where the message text comes from a variable, and the server being used required authentication?
0
No1Coder
Asked:
No1Coder
1 Solution
 
Ryan McCauleyDatabase and Reporting ManagerCommented:
When you set up the smtp profile before you can send database mail, it should let you set outgoing credentials. The sp_senddbmail stored proc doesn't let you specify a username/passworod, but those should already have been
0
 
No1CoderAuthor Commented:
this doesnt apply to the send email task.  I can send through databsde mail such as Notify operator task.  The Send Email Task does not let you specify credentiasls.
0
 
Aaron TomoskyTechnology ConsultantCommented:
When I have printers or other dumb devices that dot know how to use email correctly I get around it by running a local hmailserver. Set it up to be an smtp forwarder and put your login credentials in. Make sure you ip limit and otherwise secure this as its an open relay which can easily be used for spam. Turn off iMap and pop and anything else In hmail except smtp.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
nishant joshiTechnology Development ConsultantCommented:
Hi No1Coder,

If you want to use operator to send email below point should required:

1)create database mail profile(with required credential)
2)in Sql server agent properties go to "Alert System" and set your email profile.

after that notify email will work.

and send email task not providing any authentication its uses default.

Thanks,
Nishant
0
 
No1CoderAuthor Commented:
As I stated in my original post, I can get the notify operator to work, however I see not way to send a custom message from a variable.  Given this, notify operator will not work for me.

The Send Email task does allow the message to be gernerated from a variable, however I don't see any way to enter credentials (required by my email provider) to send email.

This seems to be a very large limitation of BI.
0
 
Aaron TomoskyTechnology ConsultantCommented:
Use a local smtp relay like hmail as I've previously stated.
0
 
No1CoderAuthor Commented:
Seems like a big limitation to BI/SQL to me, where it won;t email to servers requiring authentication.  I don't know what hmail is.  Don't really want to purchase another product just to get this working. Thanks for your help.
0
 
Aaron TomoskyTechnology ConsultantCommented:
Hmail is a free tiny email server
Hmailserver.com
Takes 5 min to setup as a gmail smtp relay. I'm happy to help
0
 
No1CoderAuthor Commented:
I sent email from script using code.  Works fine.
0
 
No1CoderAuthor Commented:
works
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now