How to use Database mail feature in SQL Server 2000

  • 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[/url]
    For tips on how to post your problems[/url]

  • 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.

  • 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

  • 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

    M&M

  • 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.

  • dawidjordaan (3/9/2010)


    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.

    Good luck, if I was worrying about security and audits, I'd flag linked servers before I'd worry about CDO.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • mtassin (3/9/2010)


    Good luck, if I was worrying about security and audits, I'd flag linked servers before I'd worry about CDO.

    LOL - You would think that. I have found that most security groups never do the due diligence and only pick a few highlights from the internet. You are then stuck having to explain (often to no avail) why their approach is wrong or meaningless.

  • You can always use vbscript to access cdosys and the db. If you can get them to give you a simple queue table, then you can have your 'e-mails' inserted as a row into this table. Then, just query a table and e-mail the records. The script could be run from any machine. Doesn't have to be the db server. Then again, if you run it on your machine, it could just pop up a messagebox or append it to a text file on your desktop.

  • Thanks for the article. Unfortunately there are plenty of 2000 servers still in production (75% of mine are 2000). It was quite a refresher course when coming from a SQL 2005 shop.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • jinlye (3/9/2010)


    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%5B/quote%5D

    Yes, you are right jinlye

    M&M

  • when I try this idea, i get the error

    Msg 15404, Level 16, State 10, Line 1

    Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002.

    'usr_testmail' - is sqlServer user

    Select * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs') works fine

    What can be the reason of the error?

  • I heard a dba state that linked servers are not reliable and can fail anytime for no reason. Is this correct?

  • I've used them for years with no issues. There are some tricks for getting some setup, but have never had any reliability issues. I have links from 2000 to 2005, 2005 x64 to 2000 x86, 2000 to DB2, 2005 to DB2, and 2000 to Active Directory.

    I have learned to use OpenQuery as much as possible to increase performance.

    Aigle de Guerre!

  • Lilita (3/10/2010)


    when I try this idea, i get the error

    Msg 15404, Level 16, State 10, Line 1

    Could not obtain information about Windows NT group/user 'usr_testmail', error code 0xffff0002.

    'usr_testmail' - is sqlServer user

    Select * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs') works fine

    What can be the reason of the error?

    Lilita,

    Are you getting this error message when you are running the dummy job.

    If so, could you kindly confirm the job owner. Please change the job owner to sa, if it is not and try.

    M&M

  • mohammed moinudheen (3/8/2010)


    Comments posted to this topic are about the item <A HREF="/articles/SQL+Server+2000/69475/">How to use Database mail feature in SQL Server 2000</A>

    Good use of linked server to make use of Database mail

    ===========================================
    Better try and fail than not to try at all...

    Database Best Practices[/url]

    SQL Server Best Practices[/url]

Viewing 15 posts - 16 through 30 (of 64 total)

You must be logged in to reply to this topic. Login to reply