SQL Server Performance

Send Report from SQL Server without using SQL Mail

Discussion in 'General DBA Questions' started by sonnysingh, Feb 8, 2007.

  1. sonnysingh Member

    Hi All

    I need to configure the mail to send daily basis Report to the specific users. I want to do it without using SQL Mail as I have read the articles on this site as well as other recommended SQL server sites(sqlteam, sqlservercentral etc..).

    so first thing need to know is what is the best and reliable method to send mail daily report file as an attachment or as mail contents without using SQL Mail?

    second ...what are the requirements and steps to implement this method. I have never done this before so i need your expert help here and unfortunately, it is very urgent. so please help.


    Thanks in advance.


  2. Kewin New Member

  3. madhuottapalam New Member

  4. Kewin New Member

    I haven't used CDONTS myself, but.. since it seems to be relying on the sp_OAx procs, I wouldn't recommend it.

    In terms of effort to setup and configure, xpsmtp seems to be way easier.
    (ofc my opinion only) =;o)

    /Kenneth
  5. sonnysingh Member

    Thanks Guys...

    I have tried XPSMTP method and tried following queries..

    After the loading software "xpsmtp80.dll", I try to ping using given sample code"

    declare @rc int
    exec @rc= master.dbo.xp_smtp_sendmail
    @server= N'10.11.0.12',
    @ping= 1
    select RC = @rc

    It's return "0" means successful but when I tried


    declare @rc int
    exec @rc= master.dbo.xp_smtp_sendmail
    @ping= 1
    select RC = @rc


    then return "1" means failure

    I have also tried the following sample...


    DECLARE @FROM NVARCHAR(4000)
    DECLARE @TO NVARCHAR(4000)
    DECLARE @SUBJECT NVARCHAR(4000)

    declare @rc int
    exec @rc = master.dbo.xp_smtp_sendmail
    @FROM= N'swarn.singhl@iap-online.net',
    @TO= N'pankaj.dhingra@iap-online.net',
    @SUBJECT= N'Good Bye MAPI & OutLook'
    select RC = @rc
    go
    It's return "1" means failure..
    what I am missing???? Help

    Thanks in advance..







  6. sonnysingh Member

    Folks

    I was missing server parameter and when I have added It's work fine 9even with attachment file)...

    Now I want to send query result (daily report) with mail as content of mail. secondly, I want to implement this whole activity as a job.. Please guide me

    FYI.. I have tested within same domain ..is it going to work as it is for another domain like yahoo or hotmail account??? If not then what need to done in order to work this out?

    Thanks in advance...
  7. sonnysingh Member

    Hi All

    Please advice...it is quite urgent

    Thanks in Advance.
  8. dineshasanka Moderator

    Create PROCEDURE [dbo].[sp_SendSMTPmail_1] (@To varchar(8000),
    @Subject varchar(255),
    @Body text = null,
    @Importance int = 1,
    @Cc varchar(8000) = null,
    @Bcc varchar(8000) = null,
    @Attachments varchar(8000) = null,
    @HTMLFormat int=0,
    @From varchar(255) = null)
    AS

    SET NOCOUNT ON

    DECLARE @object int,@hr int, @StrEnd int, @Attachment varchar(255), @return int,@Msg varchar(255)
    CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)


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


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

    if @to is not null
    EXEC @hr = sp_OASetProperty @object, 'To', @To
    if @from is not null
    EXEC @hr = sp_OASetProperty @object, 'From', @From
    if @subject is not null
    EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
    if @cc is not null
    EXEC @hr = sp_OASetProperty @object, 'cc', @cc
    if @bcc is not null
    EXEC @hr = sp_OASetProperty @object, 'bcc', @bcc


    WHILE isnull(len(@Attachments),0) > 0
    BEGIN
    SELECT @StrEnd = CASE charindex(';', @Attachments)
    WHEN 0 THEN len(@Attachments)
    ELSE charindex(';', @Attachments) - 1
    END
    SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
    SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
    DELETE #FileExists
    INSERT #FileExists
    EXEC master..xp_fileexist @Attachment
    IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
    BEGIN
    RAISERROR ('File %s does not exist. Message not sent.' , 16, 1, @Attachment)
    RETURN 1
    END
    EXEC @hr = sp_OAMethod @object, 'AddAttachment', NULL, @Attachment
    SELECT @Msg = 'File ' + @Attachment + ' attached.'
    PRINT @Msg
    END

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

    IF @hr <> 0 GOTO ObjectError

    EXEC @hr = sp_OADestroy @object
    IF @hr <> 0 GOTO ObjectError

    RETURN 0

    ObjectError:
    BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN 1
    END


    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  9. sonnysingh Member

    Thanks Dinesh

    I think I missing object 'sp_displayoaerrorinfo'.. I couldn't able to find it in any of system DBs.. is this suppose to be system object or ?..if not could you please provide me this object?







  10. sonnysingh Member

    I found the SP using BOL...

    Thanks Dinesh
  11. sonnysingh Member

    I have created the 'sp_displayoaerrorinfo' and run the stored procedure. GOT FOLLOWING ERROR...



    OLE Automation Error Information
    HRESULT: 0x80040220
    Source: CDO.Message.1
    Description: "SendUsing" ??????????


    HELP...
  12. Kewin New Member

    Ok, so which are you using now?
    You had xpsmtp going, right?
    It's no problem using xpsmtp and attach files, works like a charm.
    The one thing it doesn't support, is inline queries.
    You can't include a resultset derived from a query, you have to place that as a file first, then attach that file.

    ..the sp_OA* stuff is much more complicated...

    =;o)
    /Kenneth
  13. sonnysingh Member

    Hi Folks

    I have tried three methods since added this thread and they work fine. I can send query within mail using XP_sendmail option.

    Now, I have situation where I want to use EXE file (preferably VB) which setup as a job and trigged every 24 hours for the report. I also prefer to be a config file which should have have all the parameters such as SMTP server address, user account and passwords etc...

    Please guide me for this sort setup to send mail using SQL Server.

    Thanks in Advance...
  14. sonnysingh Member

    Thanks Kewin.....
    any advice and guidelines on my recent posted issue???
  15. Kewin New Member

    Not sure I understand what the last issue is...

    You have tried several differents methods of mailing, and they all work, but now you want to invent something on your own (VB.exe of some sorts) and use that for mailing instead?

    ..I'm currently lost.
  16. sonnysingh Member

    Thanks...

    any example of SP sending mail and setup as a job running daily on specific time??

    sonny
  17. Kewin New Member

    It depends.... which method of mailing are you currently using?

    OTOH, the generic approach is the same.

    1) Install and configure ability to send mail.
    2) Write a proc that prepares and sends mail according to your requisites
    3) Set up a job that calls the proc in 2) at your chosen time interval

    The specifics of the above depends on which 'mail-platform' you choose.
    My recommendation is xpsmtp. (see earlier posts for links and more info)

    /Kenneth
  18. sonnysingh Member

    Thanks kenneth..

    Like mentioned that I have tried all of three methods and works fine... but you can not send mail using query with XPSMTP... I will try to write SP using SQL Mail... if you have any example?? will appreciated.

    sonny
  19. Kewin New Member

    (simplified)

    create proc dbo.serverConfig
    as
    EXEC xp_sendmail 'mymailaddress', @query = 'sp_configure'
    go

    set up a job that executes the proc, and schedule it to your liking.

    Go into BOL and lookup 'SQL Mail' for examples and more indepth information on how to make it work.

    =;o)
    /Kenneth
  20. sonnysingh Member

    Thanks.....[8D]
  21. sonnysingh Member

    Hi Folks

    need further help on this..As I ma using 'SQL Server SMTP Mail XP' andit is work fine.. Now I need to configure with maintenance plan as send the mail to the relevent receiver in case of failure of job...I been through the option of 'Db Maint SMTP Alerter' on 'http://www.dbmaint.com' web site. But it has limitations.

    Is there any other way I can achieve this task?

    Thanks
  22. sonnysingh Member

    Add to this ... what about xp_sendmail.zip program from 'http://www.sqldev.net' web site. It need to replace the original xp_sendmail sp.. Is any one using this and is it safe to use?

    Plesae guide me..

    Thanks, sonny
  23. Kewin New Member

    I'm still quite confused what you're using....

    Could you provide a direct link to any file that you're curious about, and also take good care of describing it with it's correct name? (the names are very similar between different 'mailers', so it's hard to guess by an almost-correct name which one is implied)

    /Kenneth
  24. sonnysingh Member

    Kewin... thanks.
    Here it is steps of explanation.
    1. using xp_smtp_sendmail program (send mail uing smtp server) as I have install on my SQlServer.
    2. I want to send mail to required recepeints to notify jobs results

    can I use xp_smtp_sendmail with maintenance plan to send mail to the recepients?

    Thanks
  25. Anil New Member

    If I understood correctly, you have created SP and able to send the email but wanted to schedule it correct?

    Create a job in SQL Server which will execute your query as a step and in advance option create the output file.

    Change the On Success action to go to next step.

    Create second step using below systax:

    EXEC master.dbo.xp_smtp_sendmail @TO = 'xxx@yyy.com,
    @FROM = 'zzz@yyy.com',
    @subject = @sub,
    @message = 'Hi, How r u?',
    @server = '10.2.2.2'
    @attachment='Output file location'
    END

    Schedule the job daily.

    This is what you are looking for?

    Anil Kumar
  26. sonnysingh Member

    Thanks Anil...

    I have replace the xp_sendmail sp and recreate with same name with smtp version. Then add next step in the job that run on failure of the job and it's work fine so far.

    My question was to confirm that if this method give any problem later as I never used this before.

    Thanks again..
    sonny
  27. Anil New Member

    Not sure, but I am using this method since a year in my environment and didn't face any issues yet.

    Thanks

    Regards,
    Anil Kumar
  28. MohammedU New Member

    I don't think there is any issue with xp_smtp_sendmail which is fromhttp://www.sqldev.net/xp/xpsmtp.htm....because lot of people using this since long time...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  29. sonnysingh Member

    Thanks a lot folks... your's advices are always valuable..

    sonny
  30. Kewin New Member

    Aha, ok. If it's Gert's xp_smtp_sendmail, then I agree.
    I've been using it for years without any problems at all.
    It's as solid as it gets =;o)

    /Kenneth

Share This Page