March 9, 2010 at 10:03 am
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
March 9, 2010 at 10:10 am
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.
March 9, 2010 at 10:14 am
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
March 9, 2010 at 10:15 am
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
March 9, 2010 at 10:26 am
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.
March 9, 2010 at 11:23 am
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.
March 9, 2010 at 1:29 pm
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.
March 9, 2010 at 3:09 pm
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.
March 9, 2010 at 4:48 pm
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
March 9, 2010 at 9:15 pm
jinlye (3/9/2010)
Yes, you are right jinlye
M&M
March 10, 2010 at 3:52 am
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?
March 10, 2010 at 5:24 am
I heard a dba state that linked servers are not reliable and can fail anytime for no reason. Is this correct?
March 10, 2010 at 6:34 am
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!
March 10, 2010 at 10:31 am
Lilita (3/10/2010)
when I try this idea, i get the errorMsg 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
March 10, 2010 at 2:16 pm
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
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply