Will SQL 2005 allow me to nest INSERT EXEC statements?
-
Hi,
I've been meaning to upgrade to SQL 2005 for a while. I just got this error on SQL 2000:
An INSERT EXEC statement cannot be nested.
If I upgrade to SQL 2005 will it allow me to do this?
Thanks in advance,
Ian.
-
Hello
Of course you can.
I have defined a dynamic query in the @SQLStringAll variable.Then I have run this query and populate temporary table as shown below:
INSERT INTO #tmp
EXEC (@SQLStringALL )
I use this statement (EXEC ...) to obtain data from linked server (I have had to parametrize the dynamic query).
Hope this helps
Damian Widera, MCT, MCSD
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET -
Could provide some example of your code that produces the error message?
It's pretty hard to see what 'nested' means in your case without an example.
/Kenneth
-
Take a look at the article below. It could be of some help.
INSERT-EXEC section @
http://www.sommarskog.se/share_data.html
Prasad Bhogadi
www.inforaise.com -
Yeah sorry. This is what I have (names changes to hopefully make it simpler):
- The 1st stored proc (called "Summary") calls the 2nd stored proc (called "Safe") using "INSERT INTO #Temp exec Safe".
- The 2nd stored Proc ("Safe") calls the 3rd stored proc (called "Unsafe") using "INSERT INTO #Temp exec Unsafe".
This is what the error is complaining about: "INSERT EXEC statements cannot be nested".
----------------------------------
ALTER PROCEDURE Summary(@ScheduleID as int,
@ResultDefinition as varchar(8000),
@ReporterUserID as INT)
AS
BEGIN
CREATE TABLE #temp (
etc etc [same defn as returned by stored proc "Safe"]
)
-- get all data into a temp table
INSERT INTO #temp
exec Safe @ScheduleID, @ResultDefinition, @ReporterUserID
SELECT ResultText,
ResultBand,
Count(*) as 'Count',
AttemptsIsScoreDependant
FROM #temp
GROUP BY ResultText, ResultBand, AttemptsIsScoreDependant
ORDER BY ResultBand ASC
END
----------------------------------------
ALTER PROCEDURE Safe(@ScheduleID as int,
@ResultDefinition as varchar(8000),
@ReporterUserID as INT)
AS
BEGIN
CREATE TABLE #Temp (
blah - same defn as returned by Unsafe
)
-- get all data into a temp table
INSERT INTO #Temp
exec Unsafe
-- get the "role" of the person doing this report
DECLARE @Role as VarChar(10)
SET @Role = ( SELECT RoleType
FROM tblLogin
WHERE UserID = @ReporterUserID
)
IF(@Role = 'Adm')
BEGIN
-- Administrators see all Candidates (no filtering by reporting permission required
SELECT *
FROM #Temp
END
ELSE
BEGIN
IF(@Role = 'Man')
BEGIN
-- Managers see the Candidates they have permission to see
SELECT *
FROM #Temp T
JOIN tblReporterCandidate RC ON RC.CandidateUserID = T.UserID
WHERE RC.ReporterUserID = @ReporterUserID
END
-- ELSE
-- Other roles types cannot see reports
-- don't return a DataSet
END
DROP TABLE #Temp
END
-------------------------------------
ALTER PROCEDURE Unsafe (@ScheduleID as int,
@ResultDefinition as varchar(8000))
AS
BEGIN
Complex SELECT goes here...(not filtered by permission to view Candidates - this is just a list of ALL candidates)
END
---------------------------------------
Thanks for all the replies so far!
-
I am pretty sure this will fail on 2005 also.
insert exec will fail if the proc you are exec'ing is itself also using insert exec.
One kludgy way around it is to use openrowset, like this:
insert into mytable
select * from
OPENROWSET('sqloledb',
'server=asdf;trusted_connection=yes',
'set fmtonly off; exec myproc')
---------------------------------------
elsasoft.org -
Shame. I was hoping to be forced into upgrading to 2005.
Thanks anyway I'll have to redo it some other way
-
It seems you do have a nesting 'problem' - there's a lot of jumping around with temptables..
Perhaps you could consider rethinking what each proc does..?
'unsafe' looks like it could be a view just as well...
Maybe consolidate 'safe' and 'unsafe' into one proc?
/Kenneth
-
if you are in control of all the sprocs involved, then there are many ways around this problem.
however if you can't change the sproc you are calling with insert-exec, then the only way I know of around it is the kludge above. If anyone else has a way around it I'd love to hear it - I hate resorting to openrowset.
---------------------------------------
elsasoft.org -
No you are wrong
I use such statements and everything work fine
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET -
Journeyman wrote: I am pretty sure this will fail on 2005 also.
insert exec will fail if the proc you are exec'ing is itself also using insert exec.
One kludgy way around it is to use openrowset, like this:
insert into mytable
select * from
OPENROWSET('sqloledb',
'server=asdf;trusted_connection=yes',
'set fmtonly off; exec myproc')
My answers: It is not true. You are able to parametrize procedure that is to be call on the linked server. I use this often, it works on SQL 2005
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET -
Damian, I think you may be wrong there.
This fails on my 2005 sp1 server:
create table #sp_help_job ( job_id uniqueidentifier ,originating_server nvarchar(30) COLLATE Latin1_General_CI_AS ,name sysname COLLATE Latin1_General_CI_AS ,enabled tinyint ,description nvarchar(512) COLLATE Latin1_General_CI_AS ,start_step_id int ,category sysname COLLATE Latin1_General_CI_AS ,owner sysname COLLATE Latin1_General_CI_AS ,notify_level_eventlog int ,notify_level_email int ,notify_level_netsend int ,notify_level_page int ,notify_email_operator sysname COLLATE Latin1_General_CI_AS ,notify_netsend_operator sysname COLLATE Latin1_General_CI_AS ,notify_page_operator sysname COLLATE Latin1_General_CI_AS ,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 COLLATE Latin1_General_CI_AS ,current_retry_attempt int ,has_step int ,has_schedule int ,has_target int ,Type int )
insert into #sp_help_job exec msdb.dbo.sp_help_job
It fails with this error:
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 68 An INSERT EXEC statement cannot be nested.
---------------------------------------
elsasoft.org -
/Kenneth: Yeah thanks mate. That's exactly what I ended up doing (shame because it means code duplication and then making a minor change to one copy - never ideal).
Thanks for all the replies so far people
-
Hello Journeyman
Look, what construction I use and it works:
1) create a temporary table
CREATE TABLE #tmp (
[_TransactionID] uniqueidentifier
,[No] int
,[Date] datetime
,[_name] varchar(100)
,[_storeID] uniqueidentifier
,_class varchar(50)
,_dbname varchar(50)
)
2) Declare variables
DECLARE
@SQLString VARCHAR(200)
DECLARE
@SQLStringALL VARCHAR(250)
3) Create dynamic SQL query - @DBName, @StoreID, @RegisterNo are given by an user as parameter to the stored proc.
SET @SQLString ='''SELECT TOP 10 [_TransactionID],[No],[_DateAndTime] as [Date],'''''+@Storename+''''' as [_name] ,[_storeID] ,_class=''''CM_TransactionDetailsStore'''','''''+@DBName+''''' as _dbname
FROM '
+ @DBName+ '.[dbo].[UI_TransactionsList_VIEW] t WHERE Register=''''Register '+CAST(@RegisterNO as char(1))+''''' AND _StoreID = '''''+CAST(@StoreID AS varchar(50))+''''' order by [_DateAndTime] desc'''
SET @SQLStringALL = 'SELECT * FROM OPENQUERY (DC, '+@SQLString+' )'
This works, when DC is properly configured as linked server.
4) Then I just run:
INSERT INTO #tmp
EXEC (@SQLStringALL )
5) It works
Greetings
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply