hi !
    i have to send mail with link to the user who are all registered. this process is completed and i stored the information in sqlserver. but if the user didnt accept the link for three days from the sent date then have to send a reminder message as a mail to the user ! how to send the reminder message as a mail...? am working in vb.net backend sqlserver 2005
                 if anyone have a idea of solving this pls give me some idea !!

 the reply for this is 

1. You will first need to setup SQL Server so that it can send emails. Search the web for some tutorials on how to do this.
2. The stored procedure to send emails is master..xp_send_mail.
3. Create a stored procedure that accesses your tables and, if the todays date is right, creates and sends the emails.
4. Finally create a Job that runs the stored procedure at the required times. (In SQL 2000 this is located in Management -> SQL Server Agent -> Jobs)

first step completed ! what have to do for second step...?

Browse thru the following articles:

-Sending mails
http://www.sqlservercentral.com/articles/SQLMail/sqlmailusingpop3andsmtp/1577/
http://support.microsoft.com/kb/312839

-Sending mails through SP's
http://www.dotnetspider.com/resources/19638-Sending-email-through-sql-server-stored-procedure.aspx

-Conditionally send email when date is reached
http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html

Hi

Use the following stored proc to send email from SQL server 2000

but since u r using SQL server 2005 u will have to rub the following script to enable OLE Automation Procedures since by default they r disabled in sql server 2005

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then create the stored proc

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

'smtp.xxx.com'

'abc@xxx.com'

'xxxxxx'

'True'

'Port No''

'1'

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

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, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

BEGIN

IF @hr = 0

--set @output_desc = ' source: ' + @source

END

ELSE

BEGIN

SET @output_desc = ' sp_oageterrorinfo failed'

END

IF not @output_desc is NULL

SET @output_mesg = 'Error'

EXEC @hr = sp_oadestroy @imsg

To execute the stored procedure do the following

Depending on your mail settings you can use the above stored procedure to send email using smtp server

And u can schedule this sp in SQL server schedular to run on daily basis

Thats it

can you teel me that the following stored procedure is correct ?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


create Procedure [dbo].[sp_SMTPMail]
    @SenderName varchar(100),
    @SenderAddress varchar(100),
    @RecipientName varchar(100),
    @RecipientAddress varchar(100),
    @Subject varchar(200),
    @Body varchar(8000)
AS   
    SET nocount on
   
    declare @oMail int --Object reference
    declare @resultcode int
   
    EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
   
    if @resultcode = 0
    BEGIN
        EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
        EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
        EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
        EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
   
        EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
   
        EXEC sp_OADestroy @oMail
    END
   

    SET nocount off

for execution

exec sp_SMTPMail @SenderName='sender', @SenderAddress='sender@mail.com',
@RecipientName = 'recipient', @RecipientAddress = 'receipient@mail.com',
@Subject='SQL Mail Test',
@body='This is a test message from SQL Server.  Smile! It worked.'

Hi Vijay

y r u not using my stored proc it works perfectly

to make it run in SQL server 2005 u will have to run this script as i said u before

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then create my stored proc for email

 hi !

First i have run your stored procedure before that i run

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

 output is succesful

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.

while run stored procedure 

output is 

Msg 102, Level 15, State 1, Procedure sp_send_cdosysmail, Line 53
Incorrect syntax near 'configuration'.
Msg 105, Level 15, State 1, Procedure sp_send_cdosysmail, Line 97
Unclosed quotation mark after the character string '

END

EXEC @hr = sp_oadestroy @imsg
'. 

pls identify my mistake and reply me...!

U failed to remove my comments thats y

use this

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

@bodytype varchar(10),@output_desc varchar(1000) output,@output_mesg varchar(10) output

DECLARE @imsg int DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',

'abc@xxx.com'

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',

'Port No'

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',

'1'

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

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, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

IF @hr <>0 SELECT @hr

EXEC @hr = sp_oageterrorinfo null, @source out, @description out

IF @hr = 0

BEGIN

set @output_desc = @description

SET @output_desc = ' sp_oageterrorinfo failed'

IF not @output_desc is NULL

SET @output_mesg = 'Error'

 hi !

      all run perfect execpt one thing final execution

EXEC sp_send_cdosysmail @From,@To,@Subject,@Body,@BodyType, @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

output message is 

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@From".
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@out_mesg".

one more question number of stored procedures are used in your query but where the stored procedures are stored or where i have to store it  can you tell me ? pls dnt feel guilty to reply me ! i have no other way to go

EXEC sp_send_cdosysmail 'Youremail','To email, 'Subject'','Body','HTMLbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

they are all system stored procs in SQL server

 hi !

      have executed the 

EXEC sp_send_cdosysmail 'vijaybabu@ace-labs.com','vijay.playful@gmail.com', 'Subject','Body','HTMLbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

error is

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@out_desc".
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@out_mesg".
 

can u reply me...?

you will have to declare the variables before using them in this way

DECLARE @out_desc varchar(1000), @out_mesg varchar(10)

EXEC sp_send_cdosysmail 'vijaybabu@ace-labs.com','vijay.playful@gmail.com', 'Subject','Body','HTMLbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_desc

PRINT @out_mesg

 hi !

DECLARE @out_desc varchar(1000), @out_mesg varchar(10)

 

EXEC sp_send_cdosysmail 'vijaybabu@ace-labs.com','vijay.playful@gmail.com', 'Subject','Body','HTMLbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_desc

PRINT @out_mesg

have executed and the result is displayed as table

      [no column name]

1    -2147220973

 and i didnt received any mail ?? do i have to replace my server name like that in stored procedure what u sent before to receive mail...?

It means

SMTP Server noo reachable

If u switch to meesages tab u will see the meesage

What is ur smtp server

did u set it here??

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',

 hi !

whatever i changed is mentioned in bold 

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',

'smtp.ace-labs.com'

--UserName

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',

'vijaybabu@ace-labs.com'

--Password

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',

'vijay123'

--UseSSL True/False

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',

'True'

--PORT

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',

'25'

stored procedure wont have error but still received in the message tab while execution is 

(1 row(s) affected)
The transport failed to connect to the server.
Error

do i have to change some more lines in stored procedures...?

Hi

use all the settings u use to configure outlook in ur mail. i think it wont be 'smtp.ace-labs.com' since sometimes it is also mail.ace-labs.com so check that since in my previus company its was mail.xxxx.com and not smtp.xxxx.com