question

ramani127 avatar image
ramani127 asked

Need standard report via email for SQL Server 2008 R2

Hi I have sql server 2008 R2 in my environment. We use sql management studio to manage mcafee epo database. I can fetch disk usage report from Standard reports and can save it. I want to generate it with some sort of script or query so that it can be sent automatically via email to my team. I can schedule it for a daily basis. Kindly help regards Ramandeep
sql server 2008r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nep avatar image
Nep answered
Hi There, Have you got SSRS? If so you could just create the query of what you required and get the subscription service to email out at the designated time. N
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

narendba avatar image
narendba answered
You can use this script for sending the drive space usage in HTML format. SET NOCOUNT ON IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace') DROP TABLE ##_DriveSpace IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo') DROP TABLE ##_DriveInfo DECLARE @Result INT , @objFSO INT , @Drv INT , @cDrive VARCHAR(13) , @Size VARCHAR(50) , @Free VARCHAR(50) , @Label varchar(10) CREATE TABLE ##_DriveSpace ( DriveLetter CHAR(1) not null , FreeSpace VARCHAR(10) not null ) CREATE TABLE ##_DriveInfo ( DriveLetter CHAR(1) , Label varchar(10) , TotalSpace bigint , FreeSpace bigint ) INSERT INTO ##_DriveSpace EXEC master.dbo.xp_fixeddrives -- Iterate through drive letters. DECLARE curDriveLetters CURSOR FOR SELECT driveletter FROM ##_DriveSpace DECLARE @DriveLetter char(1) OPEN curDriveLetters FETCH NEXT FROM curDriveLetters INTO @DriveLetter WHILE (@@fetch_status -1) BEGIN IF (@@fetch_status -2) BEGIN SET @cDrive = 'GetDrive("' + @DriveLetter + '")' EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT IF @Result = 0 EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT IF @Result = 0 EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT IF @Result = 0 EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT IF @Result = 0 EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT IF @Result 0 EXEC sp_OADestroy @Drv EXEC sp_OADestroy @objFSO SET @Size = (CONVERT(BIGINT,@Size) / 1048576 ) SET @Free = (CONVERT(BIGINT,@Free) / 1048576 ) INSERT INTO ##_DriveInfo VALUES (@DriveLetter,@Label, @Size, @Free) END FETCH NEXT FROM curDriveLetters INTO @DriveLetter END CLOSE curDriveLetters DEALLOCATE curDriveLetters PRINT 'Drive information for server ' + @@SERVERNAME + '.' PRINT '' -- Produce report. --SELECT DriveLetter -- , Label -- , FreeSpace AS [FreeSpace MB] -- , (TotalSpace - FreeSpace) AS [UsedSpace MB] -- , TotalSpace AS [TotalSpace MB] -- , ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free] --FROM ##_DriveInfo --ORDER BY [DriveLetter] ASC --GO DECLARE @Drives_Info varchar(max) Declare @Body_Drives varchar(max) declare @TableHead varchar(max) declare @TableTail varchar(max) declare @mailitem_id as int declare @statusMsg as varchar(max) declare @Error as varchar(max) declare @Note as varchar(max) Set NoCount On; set @mailitem_id = null set @statusMsg = null set @Error = null set @Note = null Set @TableTail = ''; --HTML layout-- Set @TableHead = '' + '

Datases Status

' + '' + '' + '' + ''+ ' DriveLetter' + ' Label' + ' TotalSpaceMB' + ' UsedSpaceMB' + ' FreeSpaceMB' + --' TotalSpaceMB'+ ' PercentageFREE'; --Select information for the Report-- Select @Drives_Info= (Select DriveLetter As [TD], Label As [TD], TotalSpace As [TD], (TotalSpace - FreeSpace) AS [TD], FreeSpace As [TD], ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [TD] --PercentageFREE AS [TD] FROM ##_DriveInfo For XML raw('tr'), Elements) -- Replace the entity codes and row numbers Set @Drives_Info = Replace(@Drives_Info, '_x0020_', space(1)) Set @Drives_Info = Replace(@Drives_Info, '_x003D_', '=') Set @Drives_Info = Replace(@Drives_Info, '1', '') Set @Drives_Info = Replace(@Drives_Info, '0','') Set @Body_Drives = @TableHead + @Drives_Info+@TableTail EXECUTE msdb.dbo.sp_send_dbmail @profile_name = '' ,@recipients = '' ,@subject = '' ,@body = @Body_Drives ,@body_format = 'html' DROP TABLE ##_DriveSpace DROP TABLE ##_DriveInfo
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.