SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use Database mail feature in SQL Server 2000


How to use Database mail feature in SQL Server 2000

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: Administrators
Points: 221119 Visits: 19614
Nice trick for using Database Mail in SQL Server 2000. I must admit I was wondering how you were going to do this when reading the title.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
sknox
sknox
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5247 Visits: 3003
jinlye (3/9/2010)
From the article:
It might take a few more years before all of the existing SQL Server 2000 applications are upgraded to newer versions of SQL Server.


SQL 2000 SP4 drops out of the extended support phase in April 2013. After that MS won't be doing any security patches, so security holes won't get fixed and your enterprise is at risk. So you need to be planning on upgrading within the next 36 months. http://support.microsoft.com/lifecycle/?p1=2852


Yes. 36 months = 3 years, which handily falls within the range of "a few more years". :-P
Still, it's good to have a deadline, and better to be done well before it!
M&M
M&M
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8785 Visits: 3913
vk-kirov (3/9/2010)
most of us are still working on older, SQL Server 2000 servers

Are there any statistics on this?!


Hi VK,

I wrote this because most of my colleagues are still working on SQL server 2000. Not sure, when they plan to migrate to 2005 or higher.

Moreover, in my company, this purely depends on management. We can remind them often but finally they decide based on budget etc.

M&M
dawidjordaan@gmail.com
dawidjordaan@gmail.com
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 461
This is a great article. Do you have any comments on the security side of things here? We have to follow the CIS procedures that 'outlaw' SQL Mail. Does this workaround have any security implications?
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4852 Visits: 4408
mohammed moinudheen (3/9/2010)
I wrote this because most of my colleagues are still working on SQL server 2000. Not sure, when they plan to migrate to 2005 or higher.

Thank you for your clarification.

mohammed moinudheen (3/9/2010)
Moreover, in my company, this purely depends on management. We can remind them often but finally they decide based on budget etc.

Such a familiar situation :-)

Thanks for the article, it is a nice example of using new features in old environment :-)
mtassin
mtassin
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9826 Visits: 72521
Why not just use something like CDO or if your server is old enough CDONTS?

Basically brings Database Mail to SQL server 2000. Just for fun I was creating sp_send_dbmail stored procs on my SQL 2000 servers that used CDO to send SMTP email out. Worked great and didn't depend on linked servers Smile

I won't claim to have written it... I did modify it a bit, but I can't remember where. Smile


/**********************************************************************
* Name: usp_Send_CDOSysMail
* Author: Microsoft / customized by: Jonathan Kehayias
* Customized a bit by Mtassin for Win 2k3 compatibility
* Date: 12 April 2007
* Database: DBA_Data
*
* Purpose:
* Creates a OLE Automation object to send an email using a SMTP Server
* configured in the Parameters table of the DBA_Data database. If an
* attachment is specified, it will be added to the message.
*
* References to the CDOSYS objects are at the following MSDN Web site:
* http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
*
* Changes
**********************************************************************
* No Changes
*
**********************************************************************/
ALTER PROCEDURE [Send_CDOSysMail]
@From varchar(4000),
@To varchar(4000),
@Subject varchar(4000)=' ',
@Body varchar(4000) = ' ',
@Attachment varchar(4000) = ' ',
@Importance int,
@Priority int

AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @mailserver varchar(255)

SELECT @mailserver = 'asi-exhub-1.asi.corp'

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @mailserver

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- Check for Attachment specified and attach if necessary.
IF @Attachment IS NOT NULL
BEGIN
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @Attachment
END
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @iMsg,'fields("urn:schemas:httpmail:importance").Value',@Importance
EXEC @hr = sp_OASetProperty @iMsg,'fields("urn:schemas:httpmail:priority").Value',@Priority
-- EXEC @hr = sp_OASetProperty @iMsg, 'Priority', 1
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL


-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg






--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
JHop34
JHop34
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 224
We also use cdosys for our e-mail. Actually, most of my clients are fully upgraded to 2005 but our software still uses the cdosys stored proc that was written way back then for e-mail. I've never bothered configuring the database mail.
M&M
M&M
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8785 Visits: 3913
dawidjordaan (3/9/2010)
This is a great article. Do you have any comments on the security side of things here? We have to follow the CIS procedures that 'outlaw' SQL Mail. Does this workaround have any security implications?


Dawid,
I had configured database mail this way on few of servers. But on my servers, access control is strict, only the DBA would have sysadmin and the other team member at the most can get read access for few hours to few tables. So not sure, if there would be security implications. I didn't face issues.

If there any concerns, please share with us. Thanks.

M&M
M&M
M&M
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8785 Visits: 3913
mtassin (3/9/2010)
Why not just use something like CDO or if your server is old enough CDONTS?

Basically brings Database Mail to SQL server 2000. Just for fun I was creating sp_send_dbmail stored procs on my SQL 2000 servers that used CDO to send SMTP email out. Worked great and didn't depend on linked servers Smile

I won't claim to have written it... I did modify it a bit, but I can't remember where. Smile
[/code]


Thanks mtassin, would try this as well

M&M
dawidjordaan@gmail.com
dawidjordaan@gmail.com
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 461
I will try this out and see what the auditors have to say and report back to this forum. I hope this is something I can slip by them because this would help tremendously with my management and monitoring scripts. I can tell you now that the CDO option is also out. All OLE Automation is regarded as from the devil.

It took me months before I could convince the security team the dbmail is not the same as SQL Mail.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search