Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

How to use Database mail feature in SQL Server 2000 Expand / Collapse
Author
Message
Posted Tuesday, March 9, 2010 6:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
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
Post #879308
Posted Tuesday, March 9, 2010 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:07 PM
Points: 1,359, Visits: 1,727
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".
Still, it's good to have a deadline, and better to be done well before it!
Post #879358
Posted Tuesday, March 9, 2010 8:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
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.


Mohammed Moinudheen
Post #879400
Posted Tuesday, March 9, 2010 8:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 11:04 AM
Points: 10, Visits: 447
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?
Post #879426
Posted Tuesday, March 9, 2010 9:16 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #879464
Posted Tuesday, March 9, 2010 10:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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 :)

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

/**********************************************************************
* 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
Post #879511
Posted Tuesday, March 9, 2010 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 1, 2014 8:54 AM
Points: 46, Visits: 219
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.
Post #879523
Posted Tuesday, March 9, 2010 10:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
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.


Mohammed Moinudheen
Post #879530
Posted Tuesday, March 9, 2010 10:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
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 :)

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


Thanks mtassin, would try this as well


Mohammed Moinudheen
Post #879531
Posted Tuesday, March 9, 2010 10:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 11:04 AM
Points: 10, Visits: 447
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.
Post #879546
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse