?
Solved

An INSERT EXEC statement cannot be nested.

Posted on 2014-10-02
12
Medium Priority
?
596 Views
Last Modified: 2016-06-14
Hi all,

I have Proc which will send Heath report every week, In that procedure I have temp table  which is getting data from  "msdb.dbo.sp_help_job"  and I can't use " SELECT * FROM OPENROWSET"  because of security reasons.
Can some body help me to fix this issue.


Below is the part of query.
CREATE TABLE #jobs_status      
(      
 job_id UNIQUEIDENTIFIER,      
 originating_server NVARCHAR(30),      
 name SYSNAME,      
 enabled TINYINT,      
 description NVARCHAR(512),      
 start_step_id INT,      
 category SYSNAME,      
 owner SYSNAME,      
 notify_level_eventlog INT,      
 notify_level_email INT,      
 notify_level_netsend INT,      
 notify_level_page INT,      
 notify_email_operator SYSNAME,      
 notify_netsend_operator SYSNAME,      
 notify_page_operator SYSNAME,      
 delete_level INT,      
 date_created DATETIME,      
 date_modified DATETIME,      
 version_number INT,      
 last_run_date INT,      
 last_run_time INT,      
 last_run_outcome INT,      
 next_run_date INT,      
 next_run_time INT,      
 next_run_schedule_id INT,      
 current_execution_status INT,      
 current_execution_step SYSNAME,      
 current_retry_attempt INT,      
 has_step INT,      
 has_schedule INT,      
 has_target INT,      
 type INT    )  
   
 
/* Inserting data into temp tables */  
INSERT #jobs_status EXEC msdb.dbo.sp_help_job

Insert is failing with  below Erros

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
0
Comment
Question by:GK GK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 35

Expert Comment

by:ste5an
ID: 40356471
Yup, ugly one. You need to write your own sp_help_jp proxc
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40356882
No need for rewrite. Here's the trick:
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go

SELECT * INTO #jobs_status 
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes', 'exec msdb.dbo.sp_help_job')

SELECT * FROM #jobs_status 
GO

sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go

Open in new window

0
 
LVL 35

Expert Comment

by:ste5an
ID: 40356893
Nope, error message is (11.0.5058.0 (X64) ):

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure 'sp_is_sqlagent_starting' invokes an extended stored procedure.

Open in new window

0
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!

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40356914
ste5an, your environment it's similar to the Sniva's one?
Let him try first, please.

@Sniva: Which version of SQL Server are you using?
0
 

Author Comment

by:GK GK
ID: 40356950
Hi Vitor,

Thanks for update.

we are using SQL Server 2008R2.


Regards
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40356981
Good. I tested my script in my SQL Server 2008R2 lab and worked.
You got any issue?
0
 

Author Comment

by:GK GK
ID: 40358838
Hi Victor,

I have  problem here, Due to Security reason I'm not allowed to change present setting in Server, Can you provide the  a script which can check the Server  health status.  

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.


Regards,
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40358860
The first error it's easy to resolve. It needs to activate the 'show advanced options':
sp_configure 'show advanced options', 1
GO
reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure
go

SELECT * INTO #jobs_status 
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes', 'exec msdb.dbo.sp_help_job')

SELECT * FROM #jobs_status 
GO

sp_configure 'Ad Hoc Distributed Queries', 0
go
reconfigure
go

sp_configure 'show advanced options', 0
GO
reconfigure

Open in new window

For the second error you need to have system administrator role to execute this script. Or ask someone that has it.
0
 
LVL 2

Accepted Solution

by:
Pratik Makwana earned 1000 total points
ID: 40363617
First thing is, there's no fix for the nested INSERT..EXEC problem other than re-writing the inner procedure(s) to return their data a different way to the outer procedure (temp tables usually). If you did that, it would just lead you back to your original problem though, because the inner routines are also calling xp_sqlagent_enum_jobs.

Therefore, I think that you should just be using xp_sqlagent_enum_jobs directly, yourself, which means that you will have to address the security issue somehow. I think your choices are:

1) As you initially said, just give the user's execute access to xp_sqlagent_enum_jobs.

2) Write a wrapper procedure, local to your DB with a certificate that will allow it to execute master.dbo.xp_sqlagent_enum_jobs. Unfortuantely, I am a little weak on what all this requires.

3) Same as (2), but instead of a certificate, give it an owner that can execute master.dbo.xp_sqlagent_enum_jobs. This may be a problem because you will need to make your local DB Turstworthy, and may have to enable CrossDB-Chaining (not sure for master).

4) Write the wrapper procedure as in (2), but put it in an already trusted DB, like master or msdb with dbo as the owner. Then give your users execute-access only to it. This should work but has the problem that you (or the servers DBA's) may not like putting the proc in master or msdb, plus it makes any temp table handling a bit trickier (pretty sure it's doable though)
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40364094
Pratik is right. That's the same what I've meant, just a little bit more verbose ;)
0
 

Author Comment

by:GK GK
ID: 40375368
Hi All,

I'm rewriting Procedure with out ,INSERT..EXEC.





Thanks for support
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question