How to send Email using Sql Server 2000

  • Hi all,

    This is my 1st post in this forum.I want to develop a Email using stored procedure using Sql Server 2000.You can say that it is an job Like that I want to send Birthday Email.I also dont know how to set my sql server to send Mail.

    Can u tell me.How to do that?I cant do it.

    Plz help me.

    Thanks

    Ujjwal

  • If you follow these simple instructions, you can be up and running in no time using smtp rather than mapi.

    Mapi required server configuration, installation of outlook etc, wherelse this does not.

    http://www.sqldev.net/xp/xpsmtp.htm#Description


    ------------------------------
    The Users are always right - when I'm not wrong!

  • Thanks.

    But there is an Article on a link and they r asking money 4 subscribe it.I want to implement it from 0.So i am looking from server intregation to send mail.Plz help me.

    Thanks,

    Ujjwal

  • Are you sure you have to pay?

    I never saw anything about that and a lot of people I know are using it for free.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • well,Fine Check this lik is there.

    http://www.sqlmag.com/articles/index.cfm?articleid=38756&

  • That link is an article on xp_sendmail. It has nothing to do with the article in the original link.

    Trust me. The smtp is FREE.


    ------------------------------
    The Users are always right - when I'm not wrong!

  • hi, this is chinna.I am having outlook 2003 and sqlserver 2000 installed on my system.can i generate a mail using Trigger. please help me.

    Happy coding

  • If you plan to send mail based on a trigger, be sure to implement it so that the trigger sets a flag in some table, and then some other process (e.g. a SQL Agent job running every few minutes) sends the mail and sets the flag back. If you try to send mail directly from a trigger, you'll end up with lots of locking problems. And worse - sending e-mail from trigger means that if there is any problem with mail server, address etc., users will be unable to edit data, everything will be rolled back.

  • Thanks Vladen.then how to implement that in my code.do i need to use procedure compulsorily.i tried it but i got an error message asking me to set the default settings.how should i ?

  • Copy Blat (www.blat.org) files to system32 folder then create a DTS job that spits out the Body of your e-mail into a text file (or you can just add a @body var) then create the proc below and exec it.

    Steve

    CREATE PROCEDURE [dbo].[proc_BlatSendMail]

    @to varchar(255) = 'foo@foo.org',

    @cc varchar(255) = 'foo@fake.org',

    @from varchar(255) = 'AnyName@whatever.org',

    @subject varchar(100) = 'Title of your e-mail'

    AS

    DECLARE @command as varchar(1500)

    SET @command = ''

    SET @command = @command + 'Blat C:\YourSqlOutput.txt'

    SET @command = @command + ' -to ' + '"' + @to + '"'

    SET @command = @command + ' -cc ' + '"' + @cc + '"'

    SET @command = @command + ' -f ' + '"' + @from + '"'

    SET @command = @command + ' -subject ' + '"' + @subject + '"'

    EXEC master.dbo.xp_cmdshell @command

    GO

  • You will need to install Outlook 2000 to succesfully send emails. or else with 2003, you will need to login, and open outlook as the service account and leave it open on the server everytime to send out email.

  • Alter PROCEDURE masp_sendemail

    @From varchar(100) ,

    @To varchar(100) ,

    @Subject varchar(100),

    @Body varchar(4000)

    AS

    Declare @iMsg int

    Declare @hr int

    Declare @source varchar(255)

    Declare @description varchar(500)

    Declare @output varchar(1000)

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

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')

    RETURN

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    RETURN

    END

    END

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    -- 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', 'put your mail server'

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    -- Save the configurations to the message object.

    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    -- Set the e-mail parameters.

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

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

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

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

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

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

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

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

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

    IF @hr <>0

    BEGIN

    SELECT @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')

    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

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')

    GOTO send_cdosysmail_cleanup

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    GOTO send_cdosysmail_cleanup

    END

    END

    send_cdosysmail_cleanup:

    If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it

    BEGIN

    EXEC @hr=sp_OADestroy @iMsg

    -- handle the failure of the destroy if needed

    IF @hr <>0

    BEGIN

    select @hr

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')

    EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

    -- if sp_OAGetErrorInfo was successful, print errors

    IF @hr = 0

    BEGIN

    SELECT @output = ' Source: ' + @source

    PRINT @output

    SELECT @output = ' Description: ' + @description

    PRINT @output

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')

    END

    -- else sp_OAGetErrorInfo failed

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    RETURN

    END

    END

    END

    ELSE

    BEGIN

    PRINT ' sp_OADestroy skipped because @iMsg is NULL.'

    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')

    RETURN

    END

  • I have registered the xpsmtp80.dll using exec sp_addextendedproc and it got registered properly

    I have the folowing code

    declare @rc int

    exec @rc = master.dbo.xp_smtp_sendmail

    @FROM = N'MyEmail@MyDomain.com',

    @TO = N'MyFriend@HisDomain.com'

    select RC = @rc

    go

    After executing the above code in SQL Server 2000, I get the following error:

    Cannot load the DLL xpsmtp80.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

    Can anybody help me please.

  • I have registered the xpsmtp80.dll using exec sp_addextendedproc and it got registered properly

    I have the following code

    declare @rc int

    exec @rc = master.dbo.xp_smtp_sendmail

    @FROM = N'MyEmail@MyDomain.com',

    @TO = N'MyFriend@HisDomain.com'

    select RC = @rc

    go

    After executing the above code in SQL Server 2000, I get the following error:

    Cannot load the DLL xpsmtp80.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

    Can anybody help me please.

  • Ujjwal

    Check this

    How to send e-mail without using SQL Mail in SQL Server

    http://support.microsoft.com/kb/312839/

    Regards Ramon

Viewing 15 posts - 1 through 15 (of 22 total)

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