Error with stored proc that calls Outlook

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error with stored proc that calls Outlook

Author

Topic

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 09:18:51

I've created a C# console application that I use to send emails and do a handful of other tasks. It and the Outlook account I want to send my emails from are on one server. To allow me to execute the application (which takes a parameter) from a different server, I've created a stored procedure on that server's SQL server. Here is the simple code:

ALTER PROCEDURE [dbo].[RunEmail]
(
	@arg varchar(100)
)

AS

declare @executeCmd varchar(200)
set @executeCmd = 'C:\Development\Email\Email\bin\Debug\Email.exe "' + @arg + '"'
exec xp_cmdshell @executeCmd

When I run the @exectuteCmd line in a command prompt, it executes the Email program successfully.

When I try and call the stored procedure (on any server, including the one where the EXE is located), though, it doesn't work. I get the following error (where DeleteDLs() is the first method in the EXE and "Deleting distribution lists..." is just a Console.WriteLine message before it actually runs):

Deleting distribution lists...
System.Runtime.InteropServices.COMException (0x80080005): Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to the following error: 80080005.
at DataSupportTools.DistributionListClass.DeleteDLs() in C:\Development\DataSupportTools\DataSupportTools\DataSupportTools.cs:line 344
at SendEmail.Email.Main(String[] args) in C:\Development\Email\Email\Email.cs:line 51 Exception caught.

Does anyone have any ideas? This probably extends beyond SQL Server, but I'm not sure what type of forum to post this in. One route I tried was to change the Identity in the DCOM Config for Outlook objects from "The launching user" to "This user", but that didn't prove fruitful. Perhaps I need to "Run as..." the EXE and there is some way to do that in the stored procedure code.

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 09:24:42

this is the wrong approach on so many levels

first of all if you're using sql server 2005 it has built in Database Mail which you can use to send emails.
sql server 2000 also has built in stored procedures for sending mail like xp_sendmail, however the 2000 version need outlok installed on the server.
the 2005 version just need your smtp settings and no outlook.

if none of the above work for you then the way to go is to put your messages into a table and poll that table every N seconds with your email program to check if new mails need to be sent.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 09:31:25

That was my first approach for sending emails a long time ago, but my company blocks the port (25 I believe) that SQL Server's Database Mail uses and refuses to unblock it for security purposes.

In your second idea, wouldn't that mean my email program would have to be running constantly? That doesn't seem feasible...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 09:34:00

You can change port number with database mail.

See
http://technet.microsoft.com/en-us/library/ms187689(SQL.90).aspx

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 09:38:27

so... if you block port 25, how do you send mails otherwise?

yes, that would mean that your program should be running constantly.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 09:39:02

I feel like there was some definitive reason we couldn't use DB mail. Can you have attachments with DB mail? If not, maybe that was the reason.

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 09:39:55

Outlook doesn't use port 25 I suppose.

Having a program always running is not feasible.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 09:43:01

sp_send_dbmail suopports attachments

See
http://technet.microsoft.com/en-us/library/ms190307(SQL.90).aspx

Why don't you look for yourself by the way?

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 09:50:35

yes you can have attachments with db mail.

so how do you send mails otherwise?
outlook sends mails to the exchange server which uses smtp for sending by default. on what port are those mails sent?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 09:57:05

I had forgotten the reason that we can't use db mail, but I just discussed it with my boss. Our IT dept won't open up any ports. My boss said that Outlooks uses MAPI functionality which isn't affected by port blocking or whatever.

I remember arguing with them to open a port so I could use this a long time ago, but they refused.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 10:00:16

You can't use DBmail to check for emails, and with xp_mail you could.

E 12°55'05.63"
N 56°04'39.26"

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 10:05:23

What do you mean? All I need to do is send emails.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 10:07:40

outlook uses mapi only to communicate with exchange. outlook itself doesn't send emails. it's just a client. it sents the mail via MAPI to exchange which then sends all emails via SMTP by default.
so unless you have some Exchange connector to send emails other than smtp, you just need that port. it has to be open

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 11:19:16

Ok. I just talked to a few people about it here. Based off what one IT guy said and my understanding of what he said... Outlook has it's own internal security system that allows it to access a port, but not compromise security. Our firewall unblocks outlook from using a port. All other ports are locked down because of security measures.

I don't fully understand ports, security, etc. but this is why I'm forced to use use Outlook and SQL's database mail is not an option. Any ideas on the original question/issue?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 11:25:16

the use the deprecated SqlMail and xp_sendmail stored procedure. you'll need to install outlook on the SQL server machine.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 11:33:28

Both of those use sql mail which we just discussed isn't an option, no?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 11:42:21

wait wait... there is Database Mail and SQLMail.
Database mail was introduced in sql server 2005 and uses SMTP
SQLMail has been introduced in sql server 2000 and uses MAPI. that why you have to have Outlook installed for it to work.
and that what you should try to use if you really can't use smtp.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 11:50:15

So the deprecated SQLMail (assuming it works for 2005 and 2008) would be better to use than creating a WebService?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 12:01:40

yes.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

im1dermike
Posting Yak Master

222 Posts

Posted - 2009-02-27 : 12:33:43

Thanks spirit. I quickly got SQLMail to work. :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-27 : 12:46:47

awsome!

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources