April 24, 2008 at 1:46 pm
I want to email the results of stored procedure sp_diskspace (found @ sqlservercentral.com). How can I have the @body of this query display those results? Is this possible without creating a DTS package containing a Dynamic Properties Task? Can I somehow incorporate this first query into the coding of the sp_diskspace stored procedure?
The email sends, but has no message:
declare @rc int,
@body nvarchar(4000)
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'me@company.com',
@FROM_NAME = N'servername',
@TO = N'me@company',
@priority = N'HIGH',
@subject = N'server disk space',
@message = @body,
@type = N'text/plain',
@server = N'smtpmail.company.com'
select RC = @rc
go
Here is the stored procedure sp_diskspace:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_diskspace
AS
/*
Displays the free space,free space percentage
plus total drive size for a server
*/
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
April 25, 2008 at 4:24 am
The xp_smtp_sendmail procedure has an input parameter called @messagefile where you can specify a filename to be the body text of the email. If you BCP the results of your query to file and then use that file in @messagefile, this should give you what you're after.
:hehe:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply