I recently had to extensively use a feature in SQL I seldom used before - inserting the results of an EXEC statement directly into a table. Sounds a reasonable thing to do, right? Accidentally, I had a bunch of stored procedures calling each other, using temporary tables, and finally filling a "final" table. I finished writing the whole thing, and then - BABOOM! - it doesn't work, because "An INSERT EXEC statement cannot be nested". Huh? What?
Why? Why didn't you say so before?
Following is a quote from SQL Server Books Online for "INSERT (Transact-SQL)" :
- execute_statement
-
Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. The SELECT statement cannot contain a CTE.
If execute_statement is used with INSERT, each result set must be compatible with the columns in the table or in column_list.
execute_statement can be used to execute stored procedures on the same server or a remote server. The procedure in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server.
If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted.
Did you see any clear mention of this limitation???
No comments:
Post a Comment