Невозможно использовать инструкцию rollback внутри инструкции insert exec

SQL Server Error: 3915
Severity: 16
Event Logged or not: No
Description:
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
Severity 16 Description:
Indicates general errors that can be corrected by the user.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

Alternate Solutions

1. Restarting SQL Server Service(non production instances only)

• To Restart, Start or Stop the SQL Server instance by right click on sql server instance in SSMS or in SQL. You may need to open SSMS as administrator to start, stop the instance.

• Other ways for restarting SQL server Service

1. From SQL Configuration manager from Start menu
2. From Services in Windows server
3. From Cmd using net start and net stop

2.Checking SQL Performance metrics like CPU, Memory

Check SQL Server CPU, Memory usage, longest running queries, deadlocks etc.. using activity monitor or sp_who2.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.

Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor

It shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

For information on SQL Server Activity monitor go to https://sqlserver-dba.co.uk/sql-server-administration-basics/activity-monitor

Or using SQL Query analyzer window to run sp_who2 command which is less resource intensive and gives same information as activity monitor.

2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.

1. Open task manager to check CPU, Memory usage etc.
2. Open file explorer to check Disk space on each drive.

SQL Server Error Code and solution summary

SQL Server Error: 3915
Severity: 16
Event Logged or not: No
Description:
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

EXEC sp_configure 'show advanced options', 1
RECONFIGURE RECONFIGURE WITH override
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE RECONFIGURE WITH override
GO
SELECT * INTO #TmpTable FROM OPENROWSET(
'SQLOLEDB',
'Server=?;Trusted_Connection=Yes;',                           ---имя сервера
'SET FMTONLY OFF EXEC [?]    ?')                                 ---наименование процедуры [] и значение переменной
GO
EXEC sp_configure 'ad hoc distributed queries', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
 
 
SELECT * FROM #TmpTable

Наверх

I understand there is a regularly quoted answer that is meant to address this question, but I believe there is not enough explanation on that thread to really answer the question.

Why earlier answers are inadequate

The first (and accepted) answer simply says this is a common problem and talks about having only one active insert-exec at a time (which is only the first half of the question asked there and doesn’t address the ROLLBACK error). The given workaround is to use a table-valued function — which does not help my scenario where my stored procedure needs to update data before returning a result set.

The second answer talks about using openrowset but notes you cannot dynamically specify argument values for the stored procedure — which does not help my scenario because different users need to call my procedure with different parameters.

The third answer provides something called «the old single hash table approach» but does not explain whether it is addressing part 1 or 2 of the question, nor how it works, nor why.

No answer explains why the database is giving this error in the first place.

My use case / requirements

To give specifics for my scenario (although simplified and generic), I have procedures something like below.

In a nutshell though — the first procedure will return a result set, but before it does so, it updates a status column. Effectively these records represent records that need to be synchronised somewhere, so when you call this procedure the procedure will flag the records as being «in progress» for sync.

The second stored procedure calls that first one. Of course the second stored procedure wants to take those records and perform inserts and updates on some tables — to keep those tables in sync with whatever data was returned from the first procedure. After performing all the updates, the second procedure then calls a third procedure — within a cursor (ie. row by row on all the rows in the result set that was received from the first procedure) — for the purpose of setting the status on the source data to «in sync». That is, one by one it goes back and says «update the sync status on record id 1, to ‘in sync'» … and then record 2, and then record 3, etc.

The issue I’m having is that calling the second procedure results in the error

Msg 50000, Level 16, State 1, Procedure getValuesOuterCall, Line 484 [Batch Start Line 24]
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

but calling the first procedure directly causes no error.

Procedure 1

-- Purpose here is to return a result set,
-- but for every record in the set we want to set a status flag
-- to another value as well.

alter procedure getValues @username, @password, @target

as
begin
  set xact_abort on;
  begin try
    begin transaction;
      declare @tableVariable table (
        ...
      );

      update someOtherTable
      set something = somethingElse
      output 
        someColumns
      into @tableVariable
      from someTable
      join someOtherTable
      join etc
      where someCol = @username
      and etc
      ;

      select 
        someCols
      from @tableVariable
      ;
    commit;
  end try

  begin catch
    if @@trancount > 0 rollback;
    declare @msg nvarchar(2048) = error_message() + ' Error line: ' + CAST(ERROR_LINE() AS nvarchar(100));
    raiserror (@msg, 16, 1);
    return 55555
  end catch
end

Procedure 2

-- Purpose here is to obtain the result set from earlier procedure
-- and then do a bunch of data updates based on the result set.
-- Lastly, for each row in the set, call another procedure which will
-- update that status flag to another value.

alter procedure getValuesOuterCall @username, @password, @target

as
begin
  set xact_abort on;
  begin try
    begin transaction;

      declare @anotherTableVariable

      insert into @anotherTableVariable
      exec getValues @username = 'blah', @password = @somePass, @target = ''
      ;

      with CTE as (
        select someCols
        from @anotherTableVariable
        join someOtherTables, etc;
      )
      merge anUnrelatedTable as target
      using CTE as source
      on target.someCol = source.someCol
      when matched then update 
        target.yetAnotherCol = source.yetAnotherCol,
        etc
      when not matched then
        insert (someCols, andMoreCols, etc)
        values ((select someSubquery), source.aColumn, source.etc)
      ;

      declare @myLocalVariable int;
      declare @mySecondLocalVariable int;

      declare lcur_myCursor cursor for
        select keyColumn
        from @anotherTableVariable
        ;

      open lcur_muCursor;

      fetch lcur_myCursor into @myLocalVariable;

      while @@fetch_status = 0
        begin
          select @mySecondLocalVariable = someCol 
          from someTable 
          where someOtherCol = @myLocalVariable;

          exec thirdStoredProcForSettingStatusValues @id = @mySecondLocalVariable, etc
        end

      deallocate lcur_myCursor;
  
    commit;
  end try

  begin catch
    if @@trancount > 0 rollback;
    declare @msg nvarchar(2048) = error_message() + ' Error line: ' + CAST(ERROR_LINE() AS nvarchar(100));
    raiserror (@msg, 16, 1);
    return 55555
  end catch
end

The parts I don’t understand

Firstly, I have no explicit ‘rollback’ (well, except in the catch block) — so I have to presume that an implicit rollback is causing the issue — but it is difficult to understand where the root of this problem is; I am not even entirely sure which stored procedure is causing the issue.

Secondly, I believe the statements to set xact_abort and begin transaction are required — because in procedure 1 I am updating data before returning the result set. In procedure 2 I am updating data before I call a third procedure to update further data.

Thirdly, I don’t think procedure 1 can be converted to a table-valued function because the procedure performs a data update (which would not be allowed in a function?)

Things I have tried

I removed the table variable from procedure 2 and actually created a permanent table to store the results coming back from procedure 1. Before calling procedure 1, procedure 2 would truncate the table. I still got the rollback error.

I replaced the table variable in procedure 1 with a temporary table (ie. single #). I read the articles about how such a table persists for the lifetime of the connection, so within procedure 1 I had drop table if exists... and then create table #.... I still got the rollback error.

Lastly

I still don’t understand exactly what is the problem — what is Microsoft struggling to accomplish here? Or what is the scenario that SQL Server cannot accommodate for a requirement that appears to be fairly straightforward: One procedure returns a result set. The calling procedure wants to perform actions based on what’s in that result set. If the result set is scoped to the first procedure, then why can’t SQL Server just create a temporary copy of the result set within the scope of the second procedure so that it can be acted upon?

Or have I missed it completely and the issue has something to do with the final call to a third procedure, or maybe to do with using try ... catch — for example, perhaps the logic is totally fine but for some reason it is hitting the catch block and the rollback there is the problem (ie. so if I fix the underlying reason leading us to the catch block, all will resolve)?