Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-03
5
Medium Priority
?
303 Views
Last Modified: 2012-05-11
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
Comment
Question by:Rich-Alaska
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 3

Accepted Solution

by:
IamTheMorsa earned 668 total points
ID: 35029644
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
 

Author Comment

by:Rich-Alaska
ID: 35029727
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
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 664 total points
ID: 35029749
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
 
LVL 13

Expert Comment

by:dwkor
ID: 35029770
> 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
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 668 total points
ID: 35030520
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A safe way to clean winsxs folder from your windows server 2008 R2 editions
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question