Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Nested INTO EXEC statement in SQL Server

Jan 29 2013 12:00AM by Alpesh Patel   

Today I came across the problem.

When One my SP has Insert Into Table Exec SP_Name and doing same another SP using the Main SP.

The moment I am getting the error "An INSERT EXEC statement cannot be nested."

To resolve the Issue

Do the steps.

To Enable the Ditributed Query

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO

EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1' GO

RECONFIGURE WITH OVERRIDE GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO

To Call Nested INSERT EXEC statement using

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=SQLInstance;Trusted_Connection=yes;', 'EXEC [DBName].[Schema].[SPName] ''2012-01-01''') select * from #MyTempTable drop table #MyTempTable

Read More..   [32134 clicks]

Published under: SQL Server Tips · TSQL Tips ·  ·  · 


Alpesh Patel
36 · 5% · 1662
3
 
0
Knew
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

3  Comments  

  • This is solving a minor architecture issue by introducing a major security hole.

    commented on Jan 29 2013 6:51AM
    Marc Jellinek
    96 · 2% · 586
  • I agree with Marc. I would not do this. Enabling distributed queries might become a major security risk.

    A better approach may be to convert the called procedure into an in-line TVF (if possible) and use that instead.

    commented on Jan 29 2013 11:03AM
    Nakul Vachhrajani
    4 · 36% · 11648
  • Read http://msdn.microsoft.com/en-us/library/ms187569.aspx

    Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider.

    SQL Server administrators should enable this feature for providers that are safe to be accessed by any local login.

    commented on Jan 29 2013 2:48PM
    johan.parlevliet
    733 · 0% · 45

Your Comment


Sign Up or Login to post a comment.

"Nested INTO EXEC statement in SQL Server" rated 5 out of 5 by 3 readers
Nested INTO EXEC statement in SQL Server , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]