how to avoid the “An INSERT EXEC statement cannot be nested” exception on this situation?

when I run the following script, it runs fine:

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp
   exec sp_msforeachdb  @command1='use ?; Exec sp_helpfile;' 

but when I use my own sp_foreachdb procedure, the source code is on this link below:

A more reliable and more flexible sp_MSforeachdb

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp
   exec sp_foreachdb  @command='use ?; Exec sp_helpfile;'   

I get an exception (please note I have added exception handling on that procedure)

--EXCEPTION WAS CAUGHT--
THE ERROR NUMBER:8164

SEVERITY: 16
STATE: 1

PROCEDURE: sp_foreachdb
LINE NUMBER: 165

ERROR MESSAGE: 
An INSERT EXEC statement cannot be nested.
------------------------------------ the sql ------------------------------------

SELECT name FROM sys.databases WHERE 1=1 AND state_desc = N'ONLINE' AND is_read_only = 0
Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 239
A cursor with the name 'c' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 240
A cursor with the name 'c' does not exist.

(0 row(s) affected)

The source code for Aaron’s sp_foreachdb contains the following line:

INSERT #x EXEC sp_executesql @sql;

According to your error message:

An INSERT EXEC statement cannot be nested.

So code like that below will not be valid as it is nesting INSERT xxx EXEC xxx code.

INSERT @temp
exec sp_msforeachdb  @command1='use ?; Exec sp_helpfile;'

Connecting to your own server through distributed query methods would do the trick if sp_foreachdb would return single resultset.

create table #temp 
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);

insert into #temp
select  *
FROM OPENROWSET('SQLNCLI', 'SERVER=****;UID=****;PWD=****',
' exec sp_foreachdb  @command=''  Exec ?..sp_helpfile;''  WITH RESULT SETS         ((name varchar(255),
field varchar(255),
filename varchar(255),
filegroup varchar(255),
size varchar(255),
maxsize varchar(255),
growth varchar(255),
usage varchar(255))); ')

select * from #temp

above query will return only the first resultset of sp_foreachdb execution.
but below query will return the result for all databases in a single resultset.

create table #temp 
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);

exec sp_foreachdb  @command='INSERT INTO #temp  Exec ?..sp_helpfile;'
select * from #temp

You could try adapting Aaron’s SP by getting rid of its dynamic part. The dynamic part is supposed to build a query reading just the database names from sys.databases based on the arguments supplied. The dynamic SQL is chosen to make the query most efficient – as well as maintainable. Taking into account your specific needs, some sacrifices might be in order.

I would argue, though, that the performance might not suffer much from the rewriting I am offering below, as the sys.databases system view usually does not have very many rows, but in any event you could add OPTION (RECOMPILE) at the end. However slow it may be, though, it is likely to end up rather ugly, that I can promise.

The method of rewriting is as follows. Aaron’s procedure is building the query using a repeating pattern where a parameter value is checked and, based on the result, an additional query is added to the dynamic query, i.e. like this:

SET @sql = N'SELECT name FROM sys.databases WHERE 1=1'
+ CASE WHEN some_condition1 THEN 'AND some_filter1' ELSE '' END
+ CASE WHEN some_condition2 THEN 'AND some_filter2' ELSE '' END
+ ...

Here is how you would rewrite that:

SELECT name FROM sys.databases WHERE 1=1
AND (some_filter1 OR opposite_of_some_condition1)
AND (some_filter2 OR opposite_of_some_condition2)
AND ...
OPTION (RECOMPILE);

For instance, the @system_only controls whether the database_id IN (1,2,3,4) filter should be included, like this:

SET @sql = N'SELECT name FROM sys.databases WHERE 1=1'
    + CASE WHEN @system_only = 1 THEN
        ' AND database_id IN (1,2,3,4)'
        ELSE '' END

The rewritten query would be parametrised in this way:

SELECT name FROM sys.databases WHERE 1=1
  AND (database_id IN (1,2,3,4) OR @system_only <> 1)  -- or: @system_only = 0

Use the resulting query in the cursor directly, instead of

 SELECT CASE WHEN @suppress_quotename = 1 THEN
            db
        ELSE
            QUOTENAME(db)
        END
   FROM #x ORDER BY db

As can be seen, the final touch would need to go to the SELECT part, where the simple SELECT name of the new query would be replaced with

 SELECT CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END

Obviously, you could now also get rid of the #x table as no longer needed in the SP.

One last note concerns the specific script you are planning to run against each database. Instead of

@command1='use ?; Exec sp_helpfile;'

you could have just

@command1='Exec ?..sp_helpfile;'