An INSERT EXEC statement cannot be nested. 1

[mflancour]

I am writing a stored proc (proc1) that will need info from several tables. It just happens that this info is generated in an already existing stored proc (proc2). I tried using

INSERT INTO #temp_table
EXEC proc2

but recieved the error:
An INSERT EXEC statement cannot be nested.

How can I get this data from proc2 so I can use it in proc1?

 

[SQLDenis]

One way, change master to you database name

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #temp_table
[COLOR=blue]SELECT[/color]  * 
[COLOR=blue]FROM[/color]    OPENROWSET ([COLOR=red]'SQLOLEDB'[/color],[COLOR=red]'Server=(local);TRUSTED_CONNECTION=YES;'[/color],[COLOR=red]'set fmtonly off exec master.dbo.proc2'[/color])
[COLOR=blue]AS[/color] tbl

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mflancour]

Could not process object 'set fmtonly off exec master.dbo.proc2'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

 

[SQLDenis]

I told you to change master to your DB name

instead of master.dbo.proc2
it has to be Yourdatabasename.dbo.proc2

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mflancour]

I did what you're seeing is after having changed it back for posting I generally like to use generic names when posting.

 

[SQLDenis]

>>I did what you're seeing is after having changed it back for posting I generally like to use generic names when posting.

Okay man you got me..;-) what are you saying here

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mflancour]

Could not process object 'set fmtonly off exec db1.dbo.proc2'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

I should not have used 'master' in my post. Hopfully this will be less confusing.. Sorry

 

[SQLDenis]

Run this

[COLOR=blue]use[/color] pubs
[COLOR=blue]go[/color]

[COLOR=blue]create[/color] [COLOR=blue]proc[/color] wasabi2
[COLOR=blue]as[/color] [COLOR=blue]select[/color] 1,2,3,4
[COLOR=blue]return[/color]
[COLOR=blue]go[/color]

[COLOR=blue]SELECT[/color]  * 
[COLOR=blue]FROM[/color]    OPENROWSET ([COLOR=red]'SQLOLEDB'[/color],[COLOR=red]'Server=(local);TRUSTED_CONNECTION=YES;'[/color],[COLOR=red]'set fmtonly off exec pubs.dbo.wasabi2
[/color][COLOR=red]')
[/color][COLOR=blue]AS[/color] tbl
[COLOR=blue]go[/color]

no problem right

now run this

[COLOR=blue]alter[/color] [COLOR=blue]proc[/color] wasabi2
[COLOR=blue]as[/color]
[COLOR=blue]return[/color]
[COLOR=blue]go[/color]


[COLOR=blue]SELECT[/color]  * 
[COLOR=blue]FROM[/color]    OPENROWSET ([COLOR=red]'SQLOLEDB'[/color],[COLOR=red]'Server=(local);TRUSTED_CONNECTION=YES;'[/color],[COLOR=red]'set fmtonly off exec pubs.dbo.wasabi2
[/color][COLOR=red]')
[/color][COLOR=blue]AS[/color] tbl
[COLOR=blue]go[/color]

Now you get this error

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'set fmtonly off exec pubs.dbo.wasabi2
'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error:  OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=set fmtonly off exec pubs.dbo.wasabi2
'].

so the question is does the proc return anything?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mflancour]

Yes, maybe 12 columns with 200 rows of data.

 

[mflancour]

Ah... talked to another db guy here at work and apparently "An INSERT EXEC statement cannot be nested." is happening because proc2 uses an insert into #temp as well. Not sure if that makes a difference for the code you gave me, but maybe.

 

[SQLDenis]

I reproduced the problem...make sure that you have SET NOCOUNT ON in your procs

this will fail

[COLOR=blue]use[/color] pubs
[COLOR=blue]go[/color]

[COLOR=blue]create[/color] [COLOR=blue]proc[/color] wasabi2
[COLOR=blue]as[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #temp2 (id [COLOR=blue]int[/color],id2 [COLOR=blue]int[/color],id3 [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] #temp2
[COLOR=blue]select[/color] 1,2,3

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #temp2
[COLOR=blue]return[/color]
[COLOR=blue]go[/color]


[COLOR=blue]create[/color] [COLOR=blue]proc[/color] wasabi3
[COLOR=blue]as[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #temp3 (id [COLOR=blue]int[/color],id2 [COLOR=blue]int[/color],id3 [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] #temp3
[COLOR=blue]exec[/color] wasabi2

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #temp3
[COLOR=blue]return[/color]
[COLOR=blue]go[/color]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #temp (id [COLOR=blue]int[/color],id2 [COLOR=blue]int[/color],id3 [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] #temp
[COLOR=blue]SELECT[/color]  * 
[COLOR=blue]FROM[/color]    OPENROWSET ([COLOR=red]'SQLOLEDB'[/color],[COLOR=red]'Server=(local);TRUSTED_CONNECTION=YES;'[/color],[COLOR=red]'set fmtonly off exec pubs.dbo.wasabi3
[/color][COLOR=red]')
[/color][COLOR=blue]AS[/color] tbl

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #temp

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #temp
[COLOR=blue]drop[/color] [COLOR=blue]procedure[/color] wasabi2,wasabi3

but this will not

[COLOR=blue]use[/color] pubs
[COLOR=blue]go[/color]

[COLOR=blue]create[/color] [COLOR=blue]proc[/color] wasabi2
[COLOR=blue]as[/color]
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #temp2 (id [COLOR=blue]int[/color],id2 [COLOR=blue]int[/color],id3 [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] #temp2
[COLOR=blue]select[/color] 1,2,3

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #temp2
[COLOR=blue]return[/color]
[COLOR=blue]go[/color]


[COLOR=blue]create[/color] [COLOR=blue]proc[/color] wasabi3
[COLOR=blue]as[/color]
[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]create[/color] [COLOR=blue]table[/color] #temp3 (id [COLOR=blue]int[/color],id2 [COLOR=blue]int[/color],id3 [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] #temp3
[COLOR=blue]exec[/color] wasabi2

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #temp3
[COLOR=blue]return[/color]
[COLOR=blue]go[/color]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #temp (id [COLOR=blue]int[/color],id2 [COLOR=blue]int[/color],id3 [COLOR=blue]int[/color])
[COLOR=blue]insert[/color] #temp
[COLOR=blue]SELECT[/color]  * 
[COLOR=blue]FROM[/color]    OPENROWSET ([COLOR=red]'SQLOLEDB'[/color],[COLOR=red]'Server=(local);TRUSTED_CONNECTION=YES;'[/color],[COLOR=red]'set fmtonly off exec pubs.dbo.wasabi3
[/color][COLOR=red]')
[/color][COLOR=blue]AS[/color] tbl

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] #temp

[COLOR=blue]drop[/color] [COLOR=blue]table[/color] #temp
[COLOR=blue]drop[/color] [COLOR=blue]procedure[/color] wasabi2,wasabi3

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mflancour]

Holy crop, you're a genius! Works like a charm.

 

[SQLDenis]

Thanks for the purpely

set nocount on should be the first statement in every stored proc, your proc will run faster and you won't waste bandwith (and CPU cycles) with returning n Rows affected messages

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mflancour]

ah, was wondering what that did