Nested Exec with Output

Nested Exec with Output

Post by pschliepe » Fri, 16 Oct 1998 04:00:00

Hi all,
    This has me stymied: (MS-SQL6.5)

    exec Proc1

            exec Proc2

            exec Proc3

When I run 2 or 2 and 3, it works. When I do 1 2 3, 3 refuses a null
(this is good, but the idea is not to be getting nulls in the first
I've tried temporary variables, etc...
T-SQL books mention that if once output always output, but there's gotta
be a workaround?

Cheers, Eh



1. Resubmit: nested EXEC's & ODBC

I am having a problem when trying to execute a stored procedure as a Sql
PassThrough query from Access97.

On SQL Server the structure is like this:
    sp-a is a driver stored procedure that contains a lines something like-
        create table #temp (...)
        insert into #temp exec sp-b param1, param2, etc.
        process #temp data and other table data
        return a single result set
    sp-b is a stored procedure that contains a complex dynamic SQL statement
that is EXEC'd and returns rows to populate the #temp table.

When the sp-a driver procedure is run from ISQL-w it returns 158 rows -
which is correct.
When the sp-a driver is run as an Access SPT it only returns 48 rows.
Analysis has shown that the difference is because the insert into #temp exec
sp-b code is not inserting any rows.  Access is not returning any log
information or error messages when the SPT is run from a query window.

This appears to be an ODBC problem with running nested EXEC statements since
this design works if it is run from within ISQL-w or the Enterprise Manager.

I can not find any documented issues or limitations for this.  I am aware
that you can not nest insert-exec statements (nesting error), but since this
code works from a SQL Server query window I am surprised to see different
results when run as an Access SPT query.

I would appreciate any assistance or pointers to KB articles, etc.


2. objectPAL query problem


4. FYI!! Win95+vb#.0=EXE doesn't work on WFWG or Win3.1???

5. An INSERT EXEC statement cannot be nested.


7. Error : An INSERT EXEC statement cannot be nested

8. Technical Writer Wanted

9. An INSERT EXEC statement cannot be nested

10. Nested INSERT/EXEC statements

11. An INSERT EXEC statement cannot be nested.

12. INSERT EXEC can not be nested

13. INSERT/EXEC statement cannot be nested