x

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

more ▼

asked Aug 21, 2012 at 10:17 PM in Default

avatar image

tombiernacki
338 20 22 27

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Aug 21, 2012 at 10:54 PM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 22, 2012 at 02:32 PM

avatar image

tombiernacki
338 20 22 27

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x389
x136
x86

asked: Aug 21, 2012 at 10:17 PM

Seen: 1341 times

Last Updated: Aug 22, 2012 at 02:37 PM

Copyright 2016 Redgate Software. Privacy Policy