Solved

An INSERT EXEC statement cannot be nested.

Posted on 2014-10-02
12
284 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
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 32

Expert Comment

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

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 32

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
 
LVL 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

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 45

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 32

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 77
Managing SQL log files, SQL Server 2014 6 55
SQL Date Retrival 7 26
Test a query 23 13
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now