Error "Nested INSERT INTO ... EXECUTE"

Question

Error "Nested INSERT INTO ... EXECUTE"

Florian Reischl

SSC-Dedicated

Points: 37301
More actions
August 26, 2009 at 1:48 am

#134405
Hi my friends!
As first, sorry for not being here for a long time! I'm very busy those days...
I want to move specific data into staging tables and export them into files, which will be loaded into a data warehouse database. I have a procedure which gets all information to dynamically export specific rows of a table into a file.
I get the error message "An INSERT EXEC statement cannot be nested.". I know the meaning of the message, but I'm looking for a good solution. Maybe anybody has a design suggestion hint.
The reason for the error message:
- My procedure returns the IDs of all exported rows. I need them for further tasks.
- Within this procedure I use xp_cmdshell to export the data into files. I use the "INSERT INTO ... EXECUTE" syntax too to avoid the standard output if execution was successful. Therefore I redirect the output into an internal temp table and parse it after execution of the BCP command to check for errors. If there have been any errors I raise an error and show the returned output.
Here is a sample script and a test procedure which shows the problem.
USE tempdb
GO
---========================================================
-- Sample staging table
IF (OBJECT_ID('Stage_Sample') IS NULL)
CREATE TABLE Stage_Sample
(
Object_Id INT NOT NULL PRIMARY KEY CLUSTERED,
Name NVARCHAR(128)
)
GO
---========================================================
-- Procedure to handle the export
IF (OBJECT_ID('usp_TestNestedInsertExec') IS NULL)
EXECUTE ('CREATE PROCEDURE usp_TestNestedInsertExec AS SELECT 1')
GO
-- My procedure which uses xp_cmdshell to export data with BCP
ALTER PROCEDURE usp_TestNestedInsertExec
AS
-- Delete old staged rows
DELETE FROM Stage_Sample
-- Some sample rows to be exported
INSERT INTO Stage_Sample (
Object_Id
,Name
)
SELECT TOP(10)
object_id
,name
FROM sys.objects;
-- Table to accept BCP output
DECLARE @BcpErrors TABLE
(
Id INT NOT NULL IDENTITY PRIMARY KEY,
Msg VARCHAR(1024)
);
-- Execute xp_cmdshell with a valid BCP command line to export data into file
INSERT INTO @BcpErrors
EXECUTE xp_cmdshell 'BCP tempdb..Stage_Sample OUT C:\Test.bcp -S .\Sql2k8 -T -w';
-- Raise error if there have been any errors while command execution
IF EXISTS (SELECT TOP(1) 1 FROM @BcpErrors WHERE Msg LIKE '%Error%')
BEGIN
-- Show output information for BCP error
SELECT * FROM @BcpErrors;
-- Raise custom error
RAISERROR('Error while BCP export of table "%s"!', 11, 5, 'AnyTable');
END
SELECT Object_Id FROM Stage_Sample
GO
---========================================================
-- Call the procedure to export data
-- This works
EXECUTE usp_TestNestedInsertExec
-- This doesn't work because of a nested INSERT INTO ... EXECUTE call
DECLARE @Done TABLE (Id INT)
INSERT INTO @Done
EXECUTE usp_TestNestedInsertExec
Some options I know:
- Using "no_output" parameter for xp_cmdshell. But this seems to be no real option, since it would make it impossible to handle errors while BCP output.
- I can use BCP option "-o" to redirect its output to a file and use BULK INSERT to import the data into my temp table. Probably the best option in my opinion
- I can create a linked server to the local server and (try to) use the OPENQUERY hack to call the INSERT INTO ... EXECUTE within the query string.
- Currently I use CSV as output format. I can use a linked server as destination data source instead of BCP to export the data. But this would make it impossible to switch to another output format. Probably I need native format for later tasks since there are some multi-line text data within the database.
Any suggestions for one of those options or any other ideas would be great!
Thanks
Flo

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I think that you can use the return value of xp_cmdshell. If it is 0, then it was successful. If it returns 1, then an error occurred. Try to replace this line:

EXECUTE xp_cmdshell 'BCP tempdb..Stage_Sample OUT C:\Test.bcp -S .\Sql2k8 -T -w';

With this code

EXECUTE @Results = xp_cmdshell 'BCP tempdb..Stage_Sample OUT C:\Test.bcp -S .\Sql2k8 -T -w, no_output';

If @Results = 1—failer

Do what you need

Adi

edited - Forgot to add the no_output in my original code

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi Adi

Good idea! Just tried it. Somebody told me the return code of xp_cmdshell does not work but it works fine.

So I can add the "-o" option and either load the output information or move the file to an error directory.

Thanks

Flo