Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22

Thread: Sql Mail And Sql Server Agent Mail With Lotus Notes Domino Server

  1. #16
    Join Date
    Oct 2003
    Location
    Stockholm
    Posts
    3

    Post Complicated matter according to MS


  2. #17
    Join Date
    Jul 2004
    Posts
    16
    customise mails that are sent ?

    Hi all,
    i was unable to find how to add the actual MS SQL SERVER name in my mail subject :

    declare @Body varchar(4000)
    select @Body = 'This mail was sent by server : ' + @@SERVERNAME

    Doesn't seem to be correct syntax ?
    + how could i send the content of a specific log file (text file) in the mail body ?

    thanks Florent

  3. #18
    Join Date
    Sep 2002
    Posts
    5,938
    Tried with @attachments argument in xp_sendmail? Check books online for detailed syntax.

  4. #19
    Join Date
    Oct 2003
    Location
    Stockholm
    Posts
    3
    If you really need to do what you ask I have a tip: import the content of the textfile into a table (I use a scheduled job to run a DTS-package DTS: source textfile -> Data transformation -> destination database-table). Then you use select from the table to create the message. When using xp_sendmail I find it easier to populate the parameters first with a set-command.
    declare @mymessage varchar(255)
    set @mymessage=(select acolumn from atable where...)
    xp_sendmail @message=@mymessage
    ...

    I find that if you do like above with all parameters you need it will work better than

    xp_sendmail @message=(select...)

    Emailing the content of a textfile is quite "advanced" but I got it to work after a long time. :-)

  5. #20
    Join Date
    Jul 2004
    Posts
    16
    Well, ok for reading file.

    Now, i try to monitor freespace of my sevrer's disks, but there is something i can't understand :

    i use the "sp_send_cdosysmail" to send an email in case of low freespace but am unable to insert a variable value into a text one :


    USE Administration
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[DE_FreeSpace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [DE_FreeSpace]
    GO

    declare @MB_Free int
    declare @Body varchar(4000)

    create table DE_FreeSpace(Drive char(1), MB_Free int)

    insert into DE_FreeSpace exec master.dbo.xp_fixeddrives

    select @MB_Free = MB_Free from DE_FreeSpace where Drive = 'C'

    -- Free Space on C drive Less than Threshold
    if @MB_Free < 1024
    select @Body = 'FreeSpace of C: < 1Go on ' + @@SERVERNAME
    exec sp_send_cdosysmail 'alerts@mydomain.com', 'alertes@mydomain.com','SERVER FREE SPACE ALERT', @Body

    I receive an email without any body into it...

  6. #21
    Join Date
    Oct 2003
    Location
    Stockholm
    Posts
    3

    Thumbs up

    Congratulations. You have more free disk space than 1024 MB.

    (Increase the MB value to something very high when you test this and maybe you will get your body.)

  7. #22
    Join Date
    Jul 2004
    Posts
    16
    oupsSS....you're right...

    sorry, that permit me to learn a bit more T-SQL :

    BEGIN END and convert() ;-))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •