Solved

An INSERT EXEC statement cannot be nested.

Posted on 2014-10-02
12
506 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 34

Expert Comment

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

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 34

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 50

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 50

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 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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 250 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 34

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 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