Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

An INSERT EXEC statement cannot be nested. 1

Status
Not open for further replies.
mflancour

mflancour

MIS
Apr 23, 2002
379
US
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
Code: 
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?
 
Sort by date Sort by votes
One way, change master to you database name

Code: 
[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





 
Upvote 0 Downvote
Could not process object 'set fmtonly off exec master.dbo.proc2'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
 
Upvote 0 Downvote
I did what you're seeing is after having changed it back for posting I generally like to use generic names when posting.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Run this

Code: 
[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

Code: 
[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
Code: 
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





 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
I reproduced the problem...make sure that you have SET NOCOUNT ON in your procs

this will fail
Code: 
[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

Code: 
[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





 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
1K
mikrom
mikrom
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
774
Volvere
V
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
594
Chris Miller
Chris Miller
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
565
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
Part Id 3900 take wrong technology id as 7 and it must Be 2 because Feature Name and Value Exist?
Replies
4
Views
281
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top