• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Sending email using SQL Server Agent, stored procedure and Com object

I need to have a SQL stored procedure run periodically and send emails to users using smtp. I don't want to use the built in SQL Mail.

Please provide suggestions for commercial software
0
Rich-Alaska
Asked:
Rich-Alaska
3 Solutions
 
IamTheMorsaCommented:
Sticking with SQL mail is a good route, but I did find these, but not exp with them.  http://sql-email.software.informer.com/
0
 
Rich-AlaskaAuthor Commented:
I have IT SQL Admins complain about SQL Mail because a user needs access to MSDB in order to execute the stored procedure to send the mail.
0
 
dwkorCommented:
I'd use SQL Mail if possible. If it does not work for you for some reason, don't use the com object. This is very bad way to do that and can introduce ton of the issues - memory management, stability, etc.

Consider to either create external process that scan database and send or, at least, use CLR stored procedure that dumps results as the files and process that picks those files and process them. In the worst case, use CLR sp instead of COM but ask yourself what is the license cost for 1 SQL Server CPU vs. 1 application server cpu.

I doubt there are a lot of commercial software available because there are not so many customers who would be interested in such workflow
0
 
dwkorCommented:
> I have IT SQL Admins complain about SQL Mail because a user needs access to MSDB in order to execute the stored procedure to send the mail.

Create "proxy" stored procedure that calls sql mail in your db and impersonate
0
 
Brendt HessSenior DBACommented:
Tell the SQL Admins that they just need to think outside the box.  Basically, if your admins want to control this, then do something like the following:

1)  Create an email staging table containing the necessary data for sending an email.  Don't limit yourself in the options you allow to be added - if you leave off something because it's not used now, it will be asked for next month.

2)  Have the SQL Admins create a job that looks in this table every so often - minute, hour, five minutes, whatever is an acceptable delay for sending the mail

3)  Have the job send the emails

If the admins want to do a bit more work, they can set up triggering actions on insert into the table that could send items as it is entered.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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