[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

Insert Exec cannot be nested

I had the following statement in my stored procedure

INSERT INTO #nextMtg EXECUTE proc_nextMtgList @estID,'', 0

I got the error 'Insert Exec cannot be nested...' and I realised there is another INSERT INTO..EXECUTE stmt in the same store procedure. So I changed my line of code to

INSERT INTO #upcomMtg
Select * from openrowset('SQLOLEDB','Server=<instance\servername>;UID=<uid>;PWD=<pwd>;database=<dbname>;Trusted_Connection=yes;','EXEC <dbname>.dbo.proc_nextMtgList 526,'''', 0') AS a

Now I am getting the error 'Invalid object name #rvmyMtg'
#rvmyMtg is a temp table created in the stored procedure proc_nextMtgList.

The stored procedure proc_nextMtgList is working correctly by itself. What could be causing the invalid object error?
0
Angel02
Asked:
Angel02
  • 2
2 Solutions
 
knightEknightCommented:
TEMP tables like #rvmyMtg have a scope limited to the current connection (I believe).  But openrowset creates a new connection, so the temp table is out of scope there.
0
 
Angel02Author Commented:
So what wrong am I doing here?
0
 
lcohanDatabase AnalystCommented:
Actually you can...there is a trick with dynamic SQL and sp_executesql and OPENROWSET - let me find an example...

check this and look for the OPENROWSET:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e66c9e71-4424-4cf3-920c-6725ffc40162/

or this similar:

http://blogs.technet.com/b/wardpond/archive/2005/08/01/408502.aspx
0
 
lcohanDatabase AnalystCommented:
my bad...sorry - knightEknight is right and I just noticed now that you run the SQL Code on one Server but ....#rvmyMtg is created by a stored proc running on another server so that temp table IS in that server tempDB and not in tempdb where you run the code. Would be easier to use permanent working table so you can fully qualify them and eventualy linked servers instead.

"Now I am getting the error 'Invalid object name #rvmyMtg'
#rvmyMtg is a temp table created in the stored procedure proc_nextMtgList."
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now