An insert exec statement cannot be nested error

Question

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

Answer 1

0

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

answered Aug 21 '12 at 10:54 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 2

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 &quot;Get-WMIObject Win32_LogicalDisk -filter     &quot;DriveType=3&quot;| Format-Table DeviceID, FreeSpace, Size&quot;'; 
 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

An insert exec statement cannot be nested error

Follow this question

Related Questions