Post reply

Database Mail not sending the attached text file

  • March 10, 2009 at 8:51 am

    #956855

    I have checked the Plan History, SQL Logs and Event Logs. Nothing helpful there.

    Thanks, Kevin

  • March 10, 2009 at 11:54 am

    #956985

    You can use this query to get the name of the log file ...

    Output: nightly backup 5pm_Subplan_1_20090309181905.txt

    [font="Courier New"]    SELECT SMPP.[name] + '_' + SMPS.subplan_description + '_' +

               CONVERT(VARCHAR(8),end_time,112) +

               REPLACE(CONVERT(VARCHAR(8),end_time,108),':','') + '.txt'

          FROM dbo.sysmaintplan_log AS SMPL

    INNER JOIN dbo.sysmaintplan_subplans AS SMPS ON SMPL.subplan_id = SMPS.subplan_id

    INNER JOIN dbo.sysmaintplan_plans    AS SMPP ON SMPL.plan_id    = SMPP.id

         WHERE start_time >= '20090309'

           AND smpp.NAME LIKE 'nightly backup 5pm'[/font]

    Heh sorry would have put this out forward before; I just figured this out. I forgot I had to do something similar to this on a 2000 Server. Using this code the logfile names match with what I have.... for last 1 week. I didn't check too far out... I hope this helps you :).

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • March 10, 2009 at 1:46 pm

    #957052

    Thanks Mohit. I would have to Select the text file name (with your code) into a variable and then past the variable into the sp_send_dbmail command as the value for the @file_attachments parameter.

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'john-doe@ccpsnet.net',

    @body= 'Test Email Body',

    @subject = 'Test Database Email',

    @profile_name = 'testsmtp',

    @file_attachments = 'S:\Prod Backups\Reports\MaintenancePlanKevinTest_Subplan_1_20090309132748.txt'

  • March 10, 2009 at 1:49 pm

    #957053

    Yeap; thats how I did do it.. Orr rather will be doing it on me servers once I get free cycles LOL. I thinks I'll add this bit to me blog so I don't have to hunt for it again ^.^

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • March 10, 2009 at 1:55 pm

    #957059

    Thanks Mohit. I am still going to research why SQL 2005 DBMail would not allow you to send an attached file (created from the Report and Logging Dialog within the Maintenance Plan) via an email. If anyone knows please let me know.

  • March 10, 2009 at 1:57 pm

    #957062

    Good luck; if yaa find something please post here. I would like to know built in way of doing it so I don't have to find "alternative" methods of doing it hehe.

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

Viewing 6 posts - 16 through 20 (of 20 total)

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