an insert exec statement cannot be nested что делать

An insert exec statement cannot be nested что делать

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error

An INSERT EXEC statement cannot be nested.

heres the actual insert code:

set @SQLString = ‘EXEC ScoresGetlines ‘ + cast ( @customerID as char ( 10 ))+ ‘,’ + cast ( @programId as char ( 10 ))+ ‘,’ + ‘»‘ + @period + ‘»,NULL,NULL,0’

INSERT INTO reportData

ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID.

that still doesnt work. same error. how can this be sorted

Answers

look this article here from Erland, that´ll help you:

HTH, Jens Suessmeyer.

can you please put an example of how you changed the stored procedures into functions to make this work? openquery is not an option for me.

That is not possible if any kind of database change is involved. You cannot change the state of the database from a function, you can do it from a stored procedure. Also, you cannot use INSERT EXEC within a function.

Following scripts present 3 workarounds:

Not pretty, but they do work.

All replies

look this article here from Erland, that´ll help you:

HTH, Jens Suessmeyer.

I had the same problem. I’ve solved it using ths suggestion of my friend Maciek. If You have stored procedure which uses another stored procedure you can get this error. To solve it change that nested stored procedures to functions. That has worked for me.

It is one of the new behavior change in SQL Server 2005. Here i given the sample workaround, no need to convert your sp into function.

Before execute the following code you have to change few settings (script given as bellow).

Prerequisite

RECONFIGURE WITH OVERRIDE

Create Proc InsertIntoSp

Declare @Script Table

Lines varchar ( max )

Insert Into @Script

Exec sp_helptext ‘sp_helptext’

Select * From @Script ;

Create Table #MyScriptTable

Lines varchar ( max )

Insert Into #MyScriptTable

Msg 8164, Level 16, State 1, Procedure InsertIntoSp, Line 10

An INSERT EXEC statement cannot be nested.

Insert Into #MyScriptTable

Select * from #MyScriptTable

If I had permissions, the OPENROWSET looks like it would work. Since I don’t, would there be anything similar to table types as parameters?

Источник

An insert exec statement cannot be nested что делать

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have a problem with executing Transact-SQL at SQL2005. To disable database distribution options, folowing code is used:

CREATE TABLE #om_tmp_distdb
(
[Name] sysname,
min_distretention int,
max_distretention int,
history_retention int,
history_cleanup_agent sysname,
dist_cleanup_agent sysname,
status int,
data_folder sysname,
data_file sysname,
data_file_size int,
log_folder sysname,
log_file sysname,
log_file_size int
)

— GETTING INFO ABOUT Repl. SETTINGS

INSERT INTO #om_tmp_distdb

EXEC sp_helpdistributiondb

DECLARE @distDbName sysname

SELECT @distDbName = name COLLATE DATABASE_DEFAULT

if @distDbName COLLATE DATABASE_DEFAULT IS NOT NULL exec sp_dropdistributiondb @distDbName

IF @@ERROR = 0 exec sp_dropdistributor

DROP TABLE #om_tmp_distdb

Under SQL2K it works successfully, but when I run it at SQL2005, the error occurs:

An INSERT EXEC statement cannot be nested.

Please explain, what is a source of the problem?

Answers

Here i given the sample workaround..

Before execute the following code you have to change few settings (script given as bellow)

RECONFIGURE WITH OVERRIDE

Create Proc InsertIntoSp

Declare @Script Table

Lines varchar ( max )

Insert Into @Script

Exec sp_helptext ‘sp_helptext’

Select * From @Script ;

Create Table #MyScriptTable

Lines varchar ( max )

Insert Into #MyScriptTable

Msg 8164, Level 16, State 1, Procedure InsertIntoSp, Line 10

An INSERT EXEC statement cannot be nested.

Insert Into #MyScriptTable

Select * from #MyScriptTable

If there are lot place you have to implement this, then better you can use the LinkedServer which is pointing to the same server.

Adv: You can suppress the ConnectionString.

Try using «set fmtonly off;» at the beginning of your script.

> Although I don’t understand, why SET FMTONLY OFF required

You can use «Profiler» to see what exactly in being executed by SQL Server. There you will notice that «set fmtonly on» is part of the script and this does not work when you have temporary tables inside your sp. That is why including «set fmtonly off» as part of the script helps.

Well, I found solution myself.

To avoid processing multiple Recordsets, you may use SQLOLEDB provider instead of MSDASQL. The synthax of Connection string I found here: http://www.dbforums.com/showthread.php?t=1190454#post4448538 (thanks to Steve Kass).

So, code must look as follows:

‘ SET FMTONLY OFF exec sp_helpdistpublisher SELECT NULL as name,NULL,NULL. ‘

WHERE name IS NOT NULL

If sp-helpdistpublisher returns recordset, it’s selected out. Otherwise second select (consisting of NULL’s) is selected out, which than is filtered by WHERE clause and returns a recordset with no rows. The error then is avoided.

All replies

The sp sp_helpdistributiondb is already using INSERT INTO.. EXEC statement. As per the definition if any SP already use INSERT INTO..EXEC you can’t insert that sp values in the INSERT INTO..EXEC.

There are workaround available.

Please tell, is there any workaround for my case? I can’t change the source of system stored procedure or create my own SP to use instead.

Here i given the sample workaround..

Before execute the following code you have to change few settings (script given as bellow)

RECONFIGURE WITH OVERRIDE

Create Proc InsertIntoSp

Declare @Script Table

Lines varchar ( max )

Insert Into @Script

Exec sp_helptext ‘sp_helptext’

Select * From @Script ;

Create Table #MyScriptTable

Lines varchar ( max )

Insert Into #MyScriptTable

Msg 8164, Level 16, State 1, Procedure InsertIntoSp, Line 10

An INSERT EXEC statement cannot be nested.

Insert Into #MyScriptTable

Select * from #MyScriptTable

If there are lot place you have to implement this, then better you can use the LinkedServer which is pointing to the same server.

Adv: You can suppress the ConnectionString.

However, it’s impossible for me to use username and password in SQL script. I’ll try to use windows authentication.

For example, executing ‘exec sp_helpdistributiondb’ returns the error:

OLE DB provider «MSDASQL» for linked server «(null)» returned message «[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘#dbfiledesc’.».

OLE DB provider «MSDASQL» for linked server «(null)» returned message «[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure ».».

Msg 7350, Level 16, State 2, Line 3

Cannot get the column information from OLE DB provider «MSDASQL» for linked server «(null)».

Maybe there is any other workaround? Please, help!

Try using «set fmtonly off;» at the beginning of your script.

It helped!

Although I don’t understand, why SET FMTONLY OFF required, after addig it prior to SQL-commands, this metod began to work! Also I noticed, that after running SET FMTONLY ON in Query window all subsequent queries, that use temporary tables fail to run.

> Although I don’t understand, why SET FMTONLY OFF required

You can use «Profiler» to see what exactly in being executed by SQL Server. There you will notice that «set fmtonly on» is part of the script and this does not work when you have temporary tables inside your sp. That is why including «set fmtonly off» as part of the script helps.

Another question to the Community:

Is there any chanse to determine in Transact-SQL, how much recordsets/rows already returned by currently executing query? The examples above fail to work, saying that «the object has no columns», when SP doesn’t return dataset.

For example, if replication isn’t set up at current server, sp_helpdistpublisher stored procedure returns no dataset, and an attempt to call it using OPENROWSET causes error:

Msg 7357, Level 16, State 2, Line 1

Cannot process the object » SET FMTONLY OFF exec sp_helpdistpublisher». The OLE DB provider «MSDASQL» for linked server «(null)» indicates that either the object has no columns or the current user does not have permissions on that object.

Or maybe there are other options to workaround?

Источник

An insert exec statement cannot be nested что делать

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error

An INSERT EXEC statement cannot be nested.

heres the actual insert code:

set @SQLString = ‘EXEC ScoresGetlines ‘ + cast ( @customerID as char ( 10 ))+ ‘,’ + cast ( @programId as char ( 10 ))+ ‘,’ + ‘»‘ + @period + ‘»,NULL,NULL,0’

INSERT INTO reportData

ive tried just a simple :

insert into reportdata

exec scoreGetLines @customerId,@programID.

that still doesnt work. same error. how can this be sorted

Answers

look this article here from Erland, that´ll help you:

HTH, Jens Suessmeyer.

can you please put an example of how you changed the stored procedures into functions to make this work? openquery is not an option for me.

That is not possible if any kind of database change is involved. You cannot change the state of the database from a function, you can do it from a stored procedure. Also, you cannot use INSERT EXEC within a function.

Following scripts present 3 workarounds:

Not pretty, but they do work.

All replies

look this article here from Erland, that´ll help you:

HTH, Jens Suessmeyer.

I had the same problem. I’ve solved it using ths suggestion of my friend Maciek. If You have stored procedure which uses another stored procedure you can get this error. To solve it change that nested stored procedures to functions. That has worked for me.

It is one of the new behavior change in SQL Server 2005. Here i given the sample workaround, no need to convert your sp into function.

Before execute the following code you have to change few settings (script given as bellow).

Prerequisite

RECONFIGURE WITH OVERRIDE

Create Proc InsertIntoSp

Declare @Script Table

Lines varchar ( max )

Insert Into @Script

Exec sp_helptext ‘sp_helptext’

Select * From @Script ;

Create Table #MyScriptTable

Lines varchar ( max )

Insert Into #MyScriptTable

Msg 8164, Level 16, State 1, Procedure InsertIntoSp, Line 10

An INSERT EXEC statement cannot be nested.

Insert Into #MyScriptTable

Select * from #MyScriptTable

If I had permissions, the OPENROWSET looks like it would work. Since I don’t, would there be anything similar to table types as parameters?

Источник

Errors: «INSERT EXEC statement cannot be nested.» and «Cannot use the ROLLBACK statement within an INSERT-EXEC statement.» How to solve this?

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

13 Answers 13

This is a common issue when attempting to ‘bubble’ up data from a chain of stored procedures. A restriction in SQL Server is you can only have one INSERT-EXEC active at a time. I recommend looking at How to Share Data Between Stored Procedures which is a very thorough article on patterns to work around this type of problem.

For example a work around could be to turn Sp3 into a Table-valued function.

This is the only «simple» way to do this in SQL Server without some giant convoluted created function or executed sql string call, both of which are terrible solutions:

Note: You MUST use ‘set fmtonly off’, AND you CANNOT add dynamic sql to this either inside the openrowset call, either for the string containing your stored procedure parameters or for the table name. Thats why you have to use a temp table rather than table variables, which would have been better, as it out performs temp table in most cases.

This trick works for me.

You don’t have this problem on remote server, because on remote server, the last insert command waits for the result of previous command to execute. It’s not the case on same server.

Profit that situation for a workaround.

If you have the right permission to create a Linked Server, do it. Create the same server as linked server.

now your Sql command in the SP1 is

Believe me, it works even you have dynamic insert in SP2

I found a work around is to convert one of the prods into a table valued function. I realize that is not always possible, and introduces its own limitations. However, I have been able to always find at least one of the procedures a good candidate for this. I like this solution, because it doesn’t introduce any «hacks» to the solution.

I encountered this issue when trying to import the results of a Stored Proc into a temp table, and that Stored Proc inserted into a temp table as part of its own operation. The issue being that SQL Server does not allow the same process to write to two different temp tables at the same time.

The accepted OPENROWSET answer works fine, but I needed to avoid using any Dynamic SQL or an external OLE provider in my process, so I went a different route.

One easy workaround I found was to change the temporary table in my stored procedure to a table variable. It works exactly the same as it did with a temp table, but no longer conflicts with my other temp table insert.

Just to head off the comment I know that a few of you are about to write, warning me off Table Variables as performance killers. All I can say to you is that in 2020 it pays dividends not to be afraid of Table Variables. If this was 2008 and my Database was hosted on a server with 16GB RAM and running off 5400RPM HDDs, I might agree with you. But it’s 2020 and I have an SSD array as my primary storage and hundreds of gigs of RAM. I could load my entire company’s database to a table variable and still have plenty of RAM to spare.

Источник

Ошибки: «Оператор INSERT EXEC не может быть вложенным». и «Невозможно использовать оператор ROLLBACK в операторе INSERT-EXEC». Как это решить?

Оператор INSERT EXEC не может быть вложенным

Я попытался изменить место, execute Sp2 и он показал мне еще одну ошибку:

Невозможно использовать инструкцию ROLLBACK в инструкции INSERT-EXEC.

Это обычная проблема при попытке «всплыть» данными из цепочки хранимых процедур. Ограничение в SQL Server состоит в том, что вы можете иметь активным только один INSERT-EXEC одновременно. Я рекомендую посмотреть Как обмениваться данными между хранимыми процедурами, которая представляет собой очень подробную статью о шаблонах, позволяющих обойти этот тип проблемы.

Например, можно было бы превратить Sp3 в функцию с табличным значением.

Это единственный «простой» способ сделать это в SQL Server без какой-либо гигантской запутанной созданной функции или выполненного вызова строки sql, оба из которых являются ужасными решениями:

Примечание : вы ДОЛЖНЫ использовать ‘set fmtonly off’, И вы НЕ МОЖЕТЕ добавить к нему динамический sql ни внутри вызова openrowset, ни для строки, содержащей параметры вашей хранимой процедуры, ни для имени таблицы. Вот почему вам нужно использовать временную таблицу, а не табличные переменные, что было бы лучше, поскольку в большинстве случаев она выполняет временную таблицу.

Хорошо, поощряемый jimhark, вот пример старого подхода с использованием единой хеш-таблицы:

У меня этот трюк работает.

У вас нет этой проблемы на удаленном сервере, потому что на удаленном сервере последняя команда вставки ожидает выполнения результата предыдущей команды. На одном сервере это не так.

Воспользуйтесь этой ситуацией для обходного пути.

Если у вас есть право на создание связанного сервера, сделайте это. Создайте тот же сервер, что и связанный сервер.

теперь ваша команда Sql в SP1

Поверьте, работает даже у вас есть динамическая вставка в SP2

Я столкнулся с этой проблемой при попытке импортировать результаты хранимой процедуры во временную таблицу, и что хранимая процедура вставлена ​​во временную таблицу как часть своей собственной операции. Проблема в том, что SQL Server не позволяет одному и тому же процессу одновременно записывать в две разные временные таблицы.

Принятый ответ OPENROWSET работает нормально, но мне нужно было избегать использования динамического SQL или внешнего поставщика OLE в моем процессе, поэтому я пошел другим путем.

Источник