How to attach file to a email

Question

Post reply

How to attach file to a email

dambalaji

SSC-Addicted

Points: 421
More actions
November 12, 2002 at 12:58 pm

#158992

I have the following stored procdure created in SQL server 2000 to send email alerts . Now, I need to alter this procedure further to attach a file to the message. I tried AttachFile property. It doesn't throws me an error but attachement is not working.
Can anyone have any idea about this. Please advise.
Note : Thsi procedure use to send Email without using SQL mail.
Create PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* 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.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
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', 'smtp.microsoft.com'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', 'C:\temp_ip.txt'
-- 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
-- EXEC @hr = sp_OASetProperty @iMsg, 'AddAttachment', 'C:\temp_ip.txt'
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
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 need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

Viewing 6 posts - 1 through 5 (of 5 total)

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

Might want to take a look at this post. It address the same issue:

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=7915

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

dambalaji SSC-Addicted Points: 421 More actions · Answer 2

Greg,

When I clicked on the link , it is not taking me to the TOPIC. Can you just post me the reply.

Thanks

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

Sorry about that. Try this one:

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=7915&FORUM_ID=5&CAT_ID=1&Topic_Title=Sending%20Attachment%20with%20Cdosys&Forum_Title=Administration

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

dambalaji SSC-Addicted Points: 421 More actions · Answer 4

Greg,

Thanks. I got the solution.

This is the command to be added to attach files.

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @Err out,'C:\temp_ip.txt'

B Thomas SSC Journeyman Points: 91 More actions · Answer 5

I like the suggestions, and the ability to attach a SQL query output to an email.

I know the following works - EXEC ('master..xp_cmdshell ''isql /o' + @filename + ' /d' + @database + ' /Q"' + @sqlquery + '" /E''')

But the only problem with the above is that you need SYSADMIN access to run XP_CMDSHELL

I DO NOT want to use XP_SENDMAIL as it uses MAPI . Is there any other way to attach SQL queries ??