Câu hỏi Lỗi: "Lệnh INSERT EXEC không thể lồng nhau." Và "Không thể sử dụng câu lệnh ROLLBACK trong câu lệnh INSERT-EXEC." Làm thế nào để giải quyết vấn đề này?


Tôi có ba thủ tục lưu sẵn Sp1, Sp2 và Sp3.

Cái đầu tiên (Sp1) sẽ thực hiện lệnh thứ hai (Sp2) và lưu dữ liệu trả về @tempTB1 và cái thứ hai sẽ thực thi cái thứ ba (Sp3) và lưu dữ liệu vào @tempTB2.

Nếu tôi thực thi Sp2 nó sẽ hoạt động và nó sẽ trả về tất cả dữ liệu của tôi từ Sp3nhưng vấn đề nằm ở Sp1, khi tôi thực hiện nó, nó sẽ hiển thị lỗi này:

Câu lệnh INSERT EXEC không thể lồng nhau

Tôi đã cố gắng thay đổi vị trí của execute Sp2 và nó hiển thị cho tôi một lỗi khác:

Không thể sử dụng câu lệnh ROLLBACK   trong một câu lệnh INSERT-EXEC.


76
2017-09-25 19:36


gốc




Các câu trả lời:


Đây là một vấn đề phổ biến khi cố gắng 'bong bóng' dữ liệu từ một chuỗi các thủ tục được lưu trữ. Một hạn chế trong SQL Server là bạn chỉ có thể có một INSERT-EXEC hoạt động cùng một lúc. Tôi khuyên bạn nên xem xét Cách chia sẻ dữ liệu giữa các thủ tục được lưu trữ đó là một bài viết rất kỹ lưỡng về các mẫu làm việc xung quanh loại vấn đề này.

Ví dụ một công việc xung quanh có thể là biến Sp3 thành hàm Table-valued.


77
2017-09-25 20:18



liên kết bị hỏng HOẶC trang web không phản hồi. - SouravA


Đây là cách "đơn giản" duy nhất để làm điều này trong SQL Server mà không có một số hàm phức tạp được tạo ra phức tạp hoặc lệnh gọi sql được thực thi, cả hai đều là các giải pháp khủng khiếp:

  1. tạo bảng tạm thời
  2. openrowset dữ liệu thủ tục lưu trữ của bạn vào nó

THÍ DỤ:

INSERT INTO #YOUR_TEMP_TABLE
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off EXEC [ServerName].dbo.[StoredProcedureName] 1,2,3')

chú thích: Bạn PHẢI sử dụng 'set fmtonly off', VÀ bạn KHÔNG THỂ thêm sql động vào bên trong cuộc gọi openrowset, hoặc cho chuỗi chứa các tham số thủ tục được lưu trữ của bạn hoặc cho tên bảng. Thats lý do tại sao bạn phải sử dụng một bảng tạm thời chứ không phải là biến bảng, mà sẽ có được tốt hơn, vì nó ra thực hiện bảng tạm thời trong nhiều trường hợp.


15
2018-06-01 22:53



Nó không phải là phải sử dụng SET FMTONLY OFF. Bạn chỉ có thể thêm một IF (1 = 0) trả về một bảng trống với cùng các kiểu dữ liệu mà thủ tục thường trả về. - Guillermo Gutiérrez
Các biến Temp Tables và Table lưu trữ dữ liệu của chúng một cách khác nhau. Các biến có thể được cho là được sử dụng cho các tập kết quả nhỏ vì trình tối ưu hóa truy vấn không duy trì số liệu thống kê về các biến bảng. Vì vậy, đối với các tập dữ liệu lớn, hầu như luôn sử dụng các bảng Temp. Đây là một bài viết blog hay về nó mssqltips.com/sqlservertip/2825/… - gh9
@ gh9 có, nhưng đây là một ý tưởng khủng khiếp cho bộ kết quả lớn anyway. Việc thống kê và sử dụng một bảng thực trong cơ sở dữ liệu tạm thời có thể gây ra chi phí đáng kể. Tôi có một thủ tục trả về một recordset với 1 hàng giá trị hiện tại (truy vấn một vài bảng) và một thủ tục lưu trữ nó trong một biến bảng và so sánh nó với các giá trị trong bảng khác với cùng định dạng. Thay đổi từ bảng tạm thời thành biến bảng tăng tốc thời gian trung bình tăng từ 8ms lên 2ms, điều quan trọng khi được gọi nhiều lần trong một ngày và 100.000 lần trong một quy trình hàng đêm. - Jason Goemaat


Công việc của tôi xung quanh cho vấn đề này đã luôn luôn được sử dụng nguyên tắc mà các bảng băm băm duy nhất nằm trong phạm vi cho bất kỳ procs được gọi là. Vì vậy, tôi có một tùy chọn chuyển đổi trong các tham số proc (mặc định thiết lập để tắt). Nếu điều này được bật, proc được gọi sẽ chèn kết quả vào bảng tạm thời được tạo trong proc gọi. Tôi nghĩ rằng trong quá khứ tôi đã tiến thêm một bước nữa và đặt một số mã vào tên gọi proc để kiểm tra xem bảng băm duy nhất có tồn tại trong phạm vi hay không, nếu có thì chèn mã, nếu không trả về tập kết quả. Dường như hoạt động tốt - cách tốt nhất để chuyển các tập dữ liệu lớn giữa các procs.


4
2018-05-04 07:27



Tôi thích câu trả lời này và tôi đặt cược bạn sẽ nhận được nhiều phiếu bầu hơn nếu bạn cung cấp và ví dụ. - jimhark


Tôi tìm thấy một công việc xung quanh là để chuyển đổi một trong những prods vào một bảng có giá trị chức năng. Tôi nhận ra rằng không phải lúc nào cũng có thể, và giới thiệu những hạn chế riêng của nó. Tuy nhiên, tôi đã có thể luôn luôn tìm thấy ít nhất một trong những thủ tục một ứng cử viên tốt cho việc này. Tôi thích giải pháp này, bởi vì nó không giới thiệu bất kỳ "hack" nào cho giải pháp.


4
2017-08-19 01:59



nhưng một bất lợi là một vấn đề với xử lý ngoại lệ nếu hàm phức tạp, phải không? - Muflix


OK, được khuyến khích bởi jimhark đây là một ví dụ về phương pháp bảng băm đơn cũ: -

CREATE PROCEDURE SP3 as

BEGIN

    SELECT 1, 'Data1'
    UNION ALL
    SELECT 2, 'Data2'

END
go


CREATE PROCEDURE SP2 as

BEGIN

    if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
        INSERT INTO #tmp1
        EXEC SP3
    else
        EXEC SP3

END
go

CREATE PROCEDURE SP1 as

BEGIN

    EXEC SP2

END
GO


/*
--I want some data back from SP3

-- Just run the SP1

EXEC SP1
*/


/*
--I want some data back from SP3 into a table to do something useful
--Try run this - get an error - can't nest Execs

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

INSERT INTO #tmp1
EXEC SP1


*/

/*
--I want some data back from SP3 into a table to do something useful
--However, if we run this single hash temp table it is in scope anyway so
--no need for the exec insert

if exists (select  * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#tmp1'))
    DROP TABLE #tmp1

CREATE TABLE #tmp1 (ID INT, Data VARCHAR(20))

EXEC SP1

SELECT * FROM #tmp1

*/

3
2018-05-16 12:00





Thủ thuật này làm việc cho tôi.

Bạn không có vấn đề này trên máy chủ từ xa, bởi vì trên máy chủ từ xa, lệnh chèn cuối cùng chờ kết quả của lệnh trước đó để thực hiện. Nó không phải là trường hợp trên cùng một máy chủ.

Lợi nhuận mà tình hình cho một workaround.

Nếu bạn có quyền để tạo Máy chủ được Liên kết, hãy thực hiện nó. Tạo cùng một máy chủ với máy chủ được liên kết.

  • trong SSMS, đăng nhập vào máy chủ của bạn
  • đi tới "Đối tượng máy chủ
  • Nhấp chuột phải vào "Máy chủ được liên kết", sau đó "Máy chủ được liên kết mới"
  • trên hộp thoại, đặt tên cho máy chủ được liên kết của bạn: ví dụ: THISSERVER
  • loại máy chủ là "Nguồn dữ liệu khác"
  • Nhà cung cấp: Microsoft OLE DB Provider cho máy chủ SQL
  • Nguồn dữ liệu: IP của bạn, nó cũng có thể chỉ là một dấu chấm (.), Bởi vì nó là localhost
  • Chuyển đến tab "Bảo mật" và chọn tab thứ ba "Được tạo bằng ngữ cảnh bảo mật hiện tại của thông tin đăng nhập"
  • Bạn có thể chỉnh sửa các tùy chọn máy chủ (tab thứ 3) nếu bạn muốn
  • Nhấn OK, máy chủ được liên kết của bạn được tạo

bây giờ lệnh Sql của bạn trong SP1 là

insert into @myTempTable
exec THISSERVER.MY_DATABASE_NAME.MY_SCHEMA.SP2

Hãy tin tôi, nó hoạt động ngay cả khi bạn có chèn động trong SP2


2
2017-11-22 18:09





Tôi đã có cùng một vấn đề và mối quan tâm trên mã trùng lặp trong hai hoặc nhiều sprocs. Tôi đã kết thúc việc thêm thuộc tính bổ sung cho "chế độ". Điều này cho phép mã phổ biến tồn tại bên trong một sproc và chế độ hướng dòng chảy và tập kết quả của sproc.


1
2018-04-13 20:15





những gì về chỉ lưu trữ đầu ra cho bảng tĩnh? Như

-- SubProcedure: subProcedureName
---------------------------------
-- Save the value
DELETE lastValue_subProcedureName
INSERT INTO lastValue_subProcedureName (Value)
SELECT @Value
-- Return the value
SELECT @Value

-- Procedure
--------------------------------------------
-- get last value of subProcedureName
SELECT Value FROM lastValue_subProcedureName

nó không lý tưởng, nhưng nó rất đơn giản và bạn không cần viết lại mọi thứ.

CẬP NHẬT: giải pháp trước không hoạt động tốt với các truy vấn song song (truy cập async và multiuser) do đó bây giờ Iam sử dụng các bảng tạm thời

-- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. 
-- The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. 
-- The table cannot be referenced by the process that called the stored procedure that created the table.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NULL
CREATE TABLE #lastValue_spGetData (Value INT)

-- trigger stored procedure with special silent parameter
EXEC dbo.spGetData 1 --silent mode parameter

lồng nhau spGetData nội dung thủ tục được lưu trữ

-- Save the output if temporary table exists.
IF OBJECT_ID('tempdb..#lastValue_spGetData') IS NOT NULL
BEGIN
    DELETE #lastValue_spGetData
    INSERT INTO #lastValue_spGetData(Value)
    SELECT Col1 FROM dbo.Table1
END

 -- stored procedure return
 IF @silentMode = 0
 SELECT Col1 FROM dbo.Table1

0
2018-02-03 12:09



Nói chung, bạn không thể tạo một quảng cáo đặc biệt như bạn có thể với Bảng. Bạn sẽ cần phải mở rộng trên ví dụ của bạn với nhiều tài liệu tham khảo hơn, vì cách tiếp cận này không thực sự dễ dàng được biết hoặc được chấp nhận. Ngoài ra, nó giống với một biểu thức Lambda hơn là một thực thi SProc, mà ANSI-SQL không cho phép các cách tiếp cận Lambda Expression. - GoldBishop
Nó hoạt động nhưng tôi thấy rằng nó không hoạt động tốt với các truy vấn song song (truy cập không đồng bộ và đa người dùng). Vì vậy bây giờ Iam sử dụng cách tiếp cận bảng tạm thời. Tôi đã cập nhật câu trả lời của mình. - Muflix
Logic bảng Temp là tốt, đó là tham chiếu SProc mà tôi đã quan tâm. Sproc không thể được truy vấn trực tiếp. Các hàm có giá trị bảng có thể được truy vấn trực tiếp từ. Phải giống như bạn đã ám chỉ trong logic được cập nhật của bạn, cách tiếp cận tốt nhất là Bảng tạm thời, phiên, phiên bản hoặc toàn cầu và hoạt động từ thời điểm đó. - GoldBishop


Khai báo biến con trỏ đầu ra cho sp bên trong:

@c CURSOR VARYING OUTPUT

Sau đó, khai báo một con trỏ c tới lựa chọn bạn muốn trả về. Sau đó mở con trỏ. Sau đó đặt tham chiếu:

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
SELECT ...
OPEN c
SET @c = c 

KHÔNG đóng hoặc tái phân bổ.

Bây giờ gọi sp bên trong từ bên ngoài cung cấp một tham số con trỏ như:

exec sp_abc a,b,c,, @cOUT OUTPUT

Khi sp bên trong thực hiện, @cOUT đã sẵn sàng để tìm nạp. Lặp lại và sau đó đóng và deallocate.


0
2018-05-19 12:31





Trên SQL Server 2008 R2, tôi đã có một không phù hợp trong các cột bảng gây ra lỗi Rollback. Nó đã biến mất khi tôi cố định biến bảng sqlcmd của tôi được điền bởi câu lệnh insert-exec để khớp với lệnh được trả về bởi proc được lưu trữ. Nó thiếu org_code. Trong một tập tin cmd cửa sổ, nó tải kết quả của thủ tục lưu trữ và chọn nó.

set SQLTXT= declare @resets as table (org_id nvarchar(9), org_code char(4), ^
tin(char9), old_strt_dt char(10), strt_dt char(10)); ^
insert @resets exec rsp_reset; ^
select * from @resets;

sqlcmd -U user -P pass -d database -S server -Q "%SQLTXT%" -o "OrgReport.txt"

-1
2017-11-29 06:16



OP đã hỏi về một lỗi xảy ra khi sử dụng các câu lệnh chèn-exec trong các thủ tục được lưu trữ lồng nhau. Vấn đề của bạn sẽ trả về một lỗi khác, chẳng hạn như "Danh sách chọn cho câu lệnh INSERT chứa ít mục hơn danh sách chèn. Số lượng giá trị SELECT phải khớp với số lượng cột INSERT." - Losbear
Đây là một cảnh báo rằng có thể nhận được thông báo này một cách sai lầm. - user3448451