asked
An insert exec statement cannot be nested error
Need help understanding why I keep getting this error. below is my script SET NOCOUNT ON -- dbMail variables Declare @MailRecipients as varchar(max) set @MailRecipients =
't@example.com' Declare @MailSubject as nvarchar(255) set @MailSubject = 'Available Disk Drive Space on ' + @@SERVERNAME Declare @MailBody as nvarchar(max) --
SQLAdmin@covenantretirement.org; -- General variables --select @@servername DECLARE @availableSpace AS FLOAT, @MinAvailableSapce as float, @driveName as varchar(3), @TotalSize as float, @cnt as int set @cnt = 1 set @MinAvailableSapce = 2 exec dbo.CRC_Spaceinfo DECLARE @tbldiskSpace TABLE ( idx int identity(1,1), driveName VARCHAR(3), freeSpaceGB FLOAT, SizeGB float ) INSERT INTO @tbldiskSpace exec dbo.CRC_Spaceinfo set @cnt = @@ROWCOUNT --SELECT @availableSpace = ROUND((freeSpace)/1024,1), @driveName = driveName --FROM @tbldiskSpace WHILE(@cnt > 0) BEGIN select @availableSpace = ROUND((freeSpaceGB)/1024,1),@TotalSize= ROUND((SizeGB)/1024,1), @driveName = driveName from @tbldiskSpace where idx = @cnt set @cnt = @cnt - 1 if (@availableSpace < @MinAvailableSapce and @driveName <> 'Q') begin set @MailBody = @driveName +@TotalSize + ' -- ' + cast(@availableSpace as varchar(20)) + 'GB free space' EXEC msdb.dbo.sp_send_dbmail 'test', @recipients = @MailRecipients, @subject = @MailSubject, @body = @MailBody end END
Can you post the code of dbo.CRC_Spaceinfo please? I expect there is an insert statement in that procedure. This will be causing your problem
Below is the crc_spaceinfo stored procedure... DECLARE @DiskInfo TABLE ( DriveName VARCHAR(3) , FreeSpaceGB FLOAT , SizeGB FLOAT ) DECLARE @psinfo TABLE ( data NVARCHAR(100) ); INSERT INTO @psinfo EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'; DELETE FROM @psinfo WHERE data IS NULL OR data LIKE '%DeviceID%' OR data LIKE '%----%' OR data LIKE '%Q$'; UPDATE @psinfo SET data = REPLACE(data, ' ', ','); ; WITH DriveSpace AS ( SELECT SUBSTRING(data, 1, 2) AS [Drive] , REPLACE(( LEFT(( SUBSTRING(data, ( PATINDEX('%[0-9]%', data) ), LEN(data)) ), CHARINDEX(',', ( SUBSTRING(data, ( PATINDEX('%[0-9]%', data) ), LEN(data)) )) - 1) ), ',', '') AS [FreeSpace] , REPLACE(RIGHT(( SUBSTRING(data, ( PATINDEX('%[0-9]%', data) ), LEN(data)) ), PATINDEX('%,%', ( SUBSTRING(data, ( PATINDEX('%[0-9]%', data) ), LEN(data)) ))), ',', '') AS [Size] FROM @psinfo ) INSERT @DiskInfo ( Drivename , FreeSpaceGB , SizeGB ) SELECT Drive , CONVERT(dec(6, 2), CONVERT(dec(17, 2), FreeSpace) / ( 1024 * 1024 * 1024 )) AS FreeSpace , CONVERT(dec(6, 2), CONVERT(dec(17, 2), size) / ( 1024 * 1024 * 1024 )) AS Size FROM DriveSpace; SELECT * FROM @DiskInfo end
8 People are following this question.
