-
Complicated matter according to MS
-
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
-
Tried with @attachments argument in xp_sendmail? Check books online for detailed syntax.
-
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. :-)
-
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...
-
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.)
-
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
-
Forum Rules
|
|