Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
akarra
Starting Member
15 Posts |
 Posted - 2010-02-01 : 09:24:39
|
| when trying to execute a procedure iam getting the below errorAn INSERT EXEC statement cannot be nested. [SQLSTATE 42000] (Error 8164). The step failed.Iam trying to insert data into a temporary table as follows(the temp table is the exact copy of the sp_help_job table structure.)INSERT #jobs_status EXEC msdb.dbo.sp_help_job what could be the problem. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-01 : 09:29:02
|
| i think you're calling the main sp somewhere in insert exec so that its getting nested |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
Kanwulf
Starting Member
11 Posts |
Posted - 2010-02-01 : 10:57:15
|
| The problem is that sp_help_job calls sp_get_composite_job_info stored procedure. And sp_get_composite_job_info has several INSERT EXEC statements in its code so this is where the nesting comes from.To work you could use OPENQUERY like this:exec sp_serveroption 'MyServerName', 'data access', 'true'select * INTO #jobs_status FROM OPENQUERY( MyServerName, ' EXEC msdb.dbo.sp_help_job ') |
|
|
|
akarra
Starting Member
15 Posts |
Posted - 2010-02-01 : 14:13:26
|
| Yes it has worked. Thanks very much Kanwulf. |
|
|
|
|
|
|
Insertion into temp table problem.