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','Ser ver=<insta nce\server name>;UID= <uid>;PWD= <pwd>;data base=<dbna me>;Truste d_Connecti on=yes;',' EXEC <dbname>.dbo.proc_nextMtgL ist 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?
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','Ser
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER