Link to home
Start Free TrialLog in
Avatar of Angel02
Angel02

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Angel02
Angel02

ASKER

So what wrong am I doing here?
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial