Send mail Using SQL CDOSYS in 2005

Here I create a procedure for send automatic mail to somebody using CDOsys with attachment.
I used BCP command for export data to file and i save it some location and I use in that procedure as attachment mail.We can create a job for this email then it will automatically send without our help.
Here the statement:
create proc [procname](@FromDate varchar(20),@ToDate varchar(20),@val varchar(20))
as
Begin
Begin
Try
declare @sql_waster varchar(8000),@sql_receiver varchar(8000),@FilenameWaster varchar(200),@FilenameReceiver varchar(200)
set

@FilenameWaster='D:\Somefolder'+CONVERT(VARCHAR(30),GETDATE(),112)+'.xls'
set

@FilenameReceiver='D:\Somefolder'+CONVERT(VARCHAR(30),GETDATE(),112) +'.xls'
set

@sql_receiver = 'bcp "Select query ('''+ @FromDate+''','''+
@ToDate
+''') " queryout '+ @FilenameReceiver + ' -S servername -U username -P password -c -T '
exec

master..xp_cmdshell @sql_receiver
set @sql_waster = 'bcp "Select query ('''+ @FromDate+''','''+ @ToDate
+''') " queryout '+ @FilenameReceiver + ' -S servername -U username -P password -c -T '
exec master..xp_cmdshell @sql_waster
Declare @iMsg int , @hr int, @source varchar(255),@description varchar(500),@output varchar(1000),@To varchar(100),@CC varchar(100),@BC varchar(100), @Subject varchar(100), @Body varchar(4000)
SET @Subject = 'put ur sub' + ' - '+ CONVERT(VARCHAR(11),cast(@FromDate as datetime),103) +' '+ 'to' + ' '+ CONVERT(VARCHAR(11),cast(@ToDate as datetime),103)
SET @Body = 'Sir ,'+ 'Please find the attached files '+''+
'Thanks & Regards, This Email is computer generated. Please dont reply.'
set @To='
' );
//-->\n
xxx@xyz.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'
set

@CC='
' );
//-->\n
yyy@xyz.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'
set

@BC ='
' );
//-->\n
zzz@xyz.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'



--***** Create the CDO.Message Object *****
EXEC

@hr = sp_OACreate 'CDO.Message', @iMsg OUT
if

@hr 0
print

'message object creation failed'

--*****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'
if

@hr 0
print

'configuration failed'
-- 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', 'smtpservername'----your smtp servername like bizmail.yahoo.com
if

@hr 0
print

'IP setting failed'
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', ' mailid'--your smtp loginid
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'pwd' --your smtp password
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
-- 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
if

@hr 0
print

'to failed'
EXEC

@hr = sp_OASetProperty @iMsg, 'CC', @CC
EXEC

@hr = sp_OASetProperty @iMsg, 'BCC', @BC
EXEC

@hr = sp_OASetProperty @iMsg, 'From', '
' );
//-->\n
some@company.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'
if

@hr 0
print

'from failed'
EXEC

@hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC

@hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC

@hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @FilenameReceiver --this is attachment
EXEC

@hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @FilenameWaster
EXEC

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

@hr 0
print

'Q Send failed'
IF

@hr 0
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
EXEC

@hr = sp_OADestroy @iMsg
End

Try
Begin

catch

select Error_Message()

END CATCH
End

Automatically Running Process When SQL Server starts

Have you ever had a need to run a query or a process as soon as SQL Server starts? Possibly you want to run a cleanup or copy routine each time SQL server is started. Well if so then here are a couple of options you might considering using to accomplish just that.

The first method is to create a stored procedure and then set the procedure for autoexecution. When a stored procedure has been set for autoexecution the store procedure runs every time SQL Server starts. You use the sp_procoption system stored procedure to set a stored procedure for autoexecution. This system stored procedure is also used to turn off autoexecution.

The sp_procoption uses the following syntax to turn on a store procedure for autoexecution:

sp_procoption @ProcName = ‘YourSP',
@OptionName = 'startup',
@OptionValue = 'on'

The above code will set the stored procedure “YourSP” to autoexecute whenever SQL Server starts. The code for “YourSP” will need to be stored in the master database, since only SP’s in the master database can be set to autoexecute.

The following command is used to turn off autoexeuction for a stored procedure.

sp_procoption @ProcName = 'YourSP',
@OptionName = 'startup',
@OptionValue = 'off'