SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Sending SMTP Mail using a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

GBear
Starting Member

USA
6 Posts

Posted - 08/02/2001 :  17:13:24  Show Profile  Reply with Quote
Merkin, nice article. I've actually been doing this myself for quite some time. The things you need to do when you don't have access to your site's Web Server and CDonts is not installed. ugh!

I have been using a different component though, one from Flicks Software called OCXMail http://www.flicks.com/ASPMail/. Check it out.. have used for years on both Web and SQL servers. no probs. (no i don't work or get anything for the endorsement, just touting what i think is a good product)

In regard to the 255 char limit. For the component i use i don't go thru the process of OASetProperty. Instead i call the method and send the "properties" as parameters. This eliminates the conversion which is causing problems for some. So my method call looks like....

EXEC @Result = sp_OAMethod @objMail, SendMail, @SendResult OUT, @SMTPServer, @ToWho, @FromWho, @Subj, @MsgText

@SendResult is a varchar(500) that holds the Mail component's success/fail status. This is then used for verifying successful sends.

I look forward to the next article on this and how you are implementing the queuing. I will want to compare it to the system i have set up.

again great article. You hit the nail on the head concerning creative thinking. Sad to see developers not utilizing that more. Course we are all here to help w/ that right??

- Marc

Go to Top of Page

remote
Starting Member

United Kingdom
1 Posts

Posted - 08/07/2001 :  07:17:15  Show Profile  Visit remote's Homepage  Reply with Quote
Hi all,

OK, this Extended prcedures lark is all new to me. I'm trying to run this script on SQL Server 2000, but I get this error message:

Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 18
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.

I've not got permissions to edit the Master database - should I need them? My host probably won't allow it. What am I missing here? One interesting fact is that the exact same script is perfect on an old SQL 7 account with the same host, and the same permissions...

Thanks for any help!

Bluze

Go to Top of Page

gerbick
Starting Member

USA
11 Posts

Posted - 08/12/2001 :  22:37:26  Show Profile  Visit gerbick's Homepage  Reply with Quote
okay... now, how do I pull a trigger for this function!?

now of that, I am confused... any help? please?

quote:

FYI - Here's the modified script to get this to work with CDONTS (which is free).

Create Procedure sp_SMTPMail
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
AS
SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL

EXEC sp_OADestroy @oMail
END


SET nocount off
GO


To test, call with something like:

exec sp_SMTPMail @SenderName='You', @SenderAddress='YourName@YourMailSite.com',
@RecipientName = 'YouAgain', @RecipientAddress = 'YourName@YourMailSite.com',
@Subject='SQL Mail Test',
@body='This is a test message from SQL Server. Smile! It worked.'


The only catch is getting CDONTS installed on your SQL Server without dumping excess junk there. I found one of our SQL Servers had IIS installed on it by mistake, and CDONTS came with it.

Should this (and Merkin's Original) go into the Script Library Forum?

------------------------------------------------------------------
Contractor$ never die, they just leave for higher-paying project$.

Edited by - AjarnMark on 07/26/2001 19:43:49



___________________
gerbick
Go to Top of Page

murschell
Starting Member

5 Posts

Posted - 08/16/2001 :  18:04:02  Show Profile  Reply with Quote
How do you attach a file with CDONTS?


Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/16/2001 :  18:29:35  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Here's a link to Microsoft's documentation on CDONTS Attachments http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_attachment_object_cdonts_library_.asp?frame=true

------------------------------------------------------------------
Contractor$ never die, they just leave for higher-paying project$.
Go to Top of Page

murschell
Starting Member

5 Posts

Posted - 08/16/2001 :  18:42:42  Show Profile  Reply with Quote
I totally understand how to use it in VB, but I can not figure out the layout for it with the sp_OAMethod. I've tried a lot of layouts like:

EXEC @hr = sp_OAMethod @object, 'Attachments', 'Add Name="Test" Sources="c:\term.txt"'
    and
EXEC @hr = sp_OAMethod @object, 'AttachFile', 'c:\term.txt'

But I can't find the correct layout.

Go to Top of Page

murschell
Starting Member

5 Posts

Posted - 08/16/2001 :  19:03:59  Show Profile  Reply with Quote
I got it.

EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, 'c:\temp.txt'

Thank you for the original code.

Go to Top of Page

rudib
Starting Member

2 Posts

Posted - 08/21/2001 :  10:28:42  Show Profile  Reply with Quote
It seems to be a hot subject these days.
I just finished an extended stored procedure which send the mail to a smtp server, without need to use a COM component. You can grab the DLL and the C++ source at
http://www.babaluga.com/dl/xpsendmail.zip
you're free to improve or debug (hopefully not needed :)


Go to Top of Page

lin
Starting Member

3 Posts

Posted - 10/30/2001 :  08:38:09  Show Profile  Reply with Quote
Damian

If I want to use this stored procedure to send mail alert when database is full, how can I get that database name or error message such as "Could not allocate space for object 'TableName' in database 'DatabaseName' because the 'PRIMARY' filegroup is full."

Thanks in advance
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/30/2001 :  09:48:15  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Lin, you're going to have to find some space for your database. This impacts EVERYTHING you have running. SQL needs space to shuffle things around. If you don't have it, you're in deep (word I won't use on the site)

Mike
"A program is a device used to convert data into error messages."
Go to Top of Page

solart
Posting Yak Master

USA
148 Posts

Posted - 10/30/2001 :  11:09:19  Show Profile  Reply with Quote
I assume the cdonts.dll goes in the winnt\system32 folder. Does this DLL need to be registered. If yes, how (please). Is there someplace on the web this DLL can be downloaded from?

Many, many thanks!!!

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/30/2001 :  11:14:08  Show Profile  Visit mfemenel's Homepage  Reply with Quote
I'm sure a google search will point you towards the .dll You can register it by using regsvr32 path\dll name

Mike
"A program is a device used to convert data into error messages."
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/30/2001 :  12:21:00  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Here's Microsoft's information on Where to Acquire the CDO Libraries. You might want to get CDO 2.0 (a.k.a. CDOSYS, a.k.a. CDO for Windows 2000) instead of CDONTS (CDO for Windows NT Server) at this point in time.

-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD!
Go to Top of Page

murschell
Starting Member

5 Posts

Posted - 10/30/2001 :  12:27:05  Show Profile  Reply with Quote
You definitely want the newer version. It does not have the limitation of 255 characters for the body of the email.

Go to Top of Page

lin
Starting Member

3 Posts

Posted - 10/30/2001 :  21:24:56  Show Profile  Reply with Quote
Mfemenel

Thanks for replying but didn't helpful. I just want to get database name when the database is full wihout using SQLMail for notification. And if I know all of answer, I won't post anything in the forum.

quote:

Lin, you're going to have to find some space for your database. This impacts EVERYTHING you have running. SQL needs space to shuffle things around. If you don't have it, you're in deep (word I won't use on the site)

Mike
"A program is a device used to convert data into error messages."




Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/30/2001 :  21:35:59  Show Profile  Visit Merkin's Homepage  Reply with Quote
OK Lin

The way I see it, you have 2 options.

One is create a job that would send you an email. Then create an alert that fires when you run out of space, and calls that job. Go have a look around the "Alerts" in enterprise manager to see what I mean.

The other way, and I think this is better...run a script every night that will check to see if anything is getting too big, then send an email if it is.

nr wrote a script to do this and posted it here,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6891&FORUM_ID=6&CAT_ID=3&Topic_Title=To+check+Database+File+Growth&Forum_Title=Administration

all you would have to do is change the email code to use sp_SMTPMail instead of xp_sendmail.

Good luck!




Damian
Go to Top of Page

lin
Starting Member

3 Posts

Posted - 10/31/2001 :  02:59:17  Show Profile  Reply with Quote
I already create a job since I have read this subject at the first time, but I can't not specify which's database cause of the event when I run out of disk space. (Cuz I use custom alert using your SMTP Mail) Anyway, your another suggestion about checking Database File Growth is a good idea and I already finish fetching each database usage. But the result of fetching look like this:

database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
a 15.00 MB 7.76 MB


reserved data index_size unused
------------------ ------------------ ------------------ ------------------
7416 KB 5224 KB 1208 KB 984 KB

database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
aa 60.00 MB 47.03 MB


reserved data index_size unused
------------------ ------------------ ------------------ ------------------
13280 KB 7960 KB 4248 KB 1072 KB

database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
aaa 40.00 MB 36.20 MB


reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3888 KB 2080 KB 1376 KB 432 KB

How can I send this result to @Body variable? I have tried to but I got 0 in the body of email.

Many thanks

quote:

OK Lin

The way I see it, you have 2 options.

One is create a job that would send you an email. Then create an alert that fires when you run out of space, and calls that job. Go have a look around the "Alerts" in enterprise manager to see what I mean.

The other way, and I think this is better...run a script every night that will check to see if anything is getting too big, then send an email if it is.

nr wrote a script to do this and posted it here,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6891&FORUM_ID=6&CAT_ID=3&Topic_Title=To+check+Database+File+Growth&Forum_Title=Administration

all you would have to do is change the email code to use sp_SMTPMail instead of xp_sendmail.

Good luck!




Damian



Go to Top of Page

solart
Posting Yak Master

USA
148 Posts

Posted - 10/31/2001 :  10:14:00  Show Profile  Reply with Quote
I have successfully used the xpsendmail.dll provided by: http://www.babaluga.com/dl/xpsendmail.zip

The install is very simple and it worked the first time. DLL does not need to be registered. I had this process up and running in about 15 minutes after downloading the zip file. As best as I can tell from looking at the source code, the message body supports 8k. You will need to embed CRLF in the message to keep it from being all one line (which I did, again with no trouble).

My thanks to the developer of this process!!


Go to Top of Page

wlk78
Starting Member

1 Posts

Posted - 12/07/2001 :  04:29:12  Show Profile  Reply with Quote

I am trying to user the extended storedprocedur writen by one of the member, the source can be get at the url below :

http://www.babaluga.com/dl/xpsendmail.zip

to execute the stored procedure, i have follow the instruction

EXEC xp_sendSMTP 'SMTP.server', 'from', 'to', 'subject', 'body'

can i know what is smtp server what should i put in ???

now i am using windows 2000 server, it comes together with smtp service, is smtp services same with smtp server, or i need to install another smtp server ?????

can anyone reply me as soon as possible .....


Go to Top of Page

rudib
Starting Member

2 Posts

Posted - 01/03/2002 :  04:12:10  Show Profile  Reply with Quote
You can use the SMTP service. A SMTP server is just a machine running a deamon dedicated to exchange mail. Most of the "public" SMTP servers will not allow you to send mail through them (that is called relaying). If you've an ISP, probably you can use its SMTP server

quote:


EXEC xp_sendSMTP 'SMTP.server', 'from', 'to', 'subject', 'body'

can i know what is smtp server what should i put in ???

now i am using windows 2000 server, it comes together with smtp service, is smtp services same with smtp server, or i need to install another smtp server ?????




Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000