Pertanyaan Kesalahan: "INSERT EXEC pernyataan tidak dapat disarangkan." Dan "Tidak dapat menggunakan pernyataan ROLLBACK dalam pernyataan INSERT-EXEC." Bagaimana mengatasi ini?


Saya memiliki tiga prosedur tersimpan Sp1, Sp2 dan Sp3.

Yang pertama (Sp1) akan mengeksekusi yang kedua (Sp2) dan menyimpan data yang dikembalikan ke dalam @tempTB1 dan yang kedua akan mengeksekusi yang ketiga (Sp3) dan menyimpan data ke dalamnya @tempTB2.

Jika saya mengeksekusi Sp2 itu akan bekerja dan itu akan mengembalikan semua data saya dari internet Sp3, tetapi masalahnya ada di Sp1, ketika saya menjalankannya maka akan muncul kesalahan ini:

INSERT EXEC statement tidak dapat diulang

Saya mencoba mengubah tempat execute Sp2 dan itu menampilkan saya kesalahan lain:

Tidak dapat menggunakan pernyataan ROLLBACK   dalam pernyataan INSERT-EXEC.


76
2017-09-25 19:36


asal


Jawaban:


Ini adalah masalah umum ketika mencoba untuk 'menggulung' data dari rantai prosedur tersimpan. Pembatasan dalam SQL Server adalah Anda hanya dapat memiliki satu INSERT-EXEC aktif pada satu waktu. Saya sarankan untuk melihat Cara Membagi Data Diantara Prosedur Tersimpan yang merupakan artikel yang sangat menyeluruh tentang pola untuk mengatasi masalah semacam ini.

Misalnya pekerjaan di sekitar bisa mengubah Sp3 menjadi fungsi bernilai Tabel.


77
2017-09-25 20:18



Ini adalah satu-satunya cara "sederhana" untuk melakukan ini di SQL Server tanpa beberapa fungsi raksasa yang berbelit-belit atau panggilan string string sql, keduanya merupakan solusi yang mengerikan:

  1. buat tabel temp
  2. buka data prosedur tersimpan Anda ke dalamnya

CONTOH:

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

Catatan: Anda HARUS menggunakan 'set fmtonly off', DAN Anda TIDAK BISA menambahkan dynamic sql ke ini baik di dalam panggilan openrowset, baik untuk string yang berisi parameter prosedur yang tersimpan atau untuk nama tabel. Itulah mengapa Anda harus menggunakan tabel temp daripada variabel tabel, yang akan lebih baik, karena melakukan tabel temp di kebanyakan kasus.


15
2018-06-01 22:53



Pekerjaan saya di sekitar untuk masalah ini selalu menggunakan prinsip bahwa tabel hash temp tunggal berada dalam ruang lingkup untuk setiap procs yang disebut. Jadi, saya memiliki opsi beralih dalam parameter proc (default diatur ke off). Jika ini diaktifkan, proc yang disebut akan memasukkan hasil ke dalam temp table yang dibuat di proc panggilan. Saya pikir di masa lalu saya telah mengambil langkah lebih lanjut dan memasukkan beberapa kode dalam proc yang disebut untuk memeriksa apakah tabel hash tunggal ada dalam ruang lingkup, jika tidak maka masukkan kode, jika tidak mengembalikan hasil yang ditetapkan. Tampaknya berfungsi dengan baik - cara terbaik untuk mengirimkan kumpulan data besar antar procs.


4
2018-05-04 07:27



Saya menemukan pekerjaan di sekitar adalah untuk mengubah salah satu prods menjadi fungsi berharga meja. Saya menyadari bahwa itu tidak selalu mungkin, dan memperkenalkan keterbatasannya sendiri. Namun, saya selalu dapat menemukan setidaknya satu prosedur yang merupakan kandidat yang baik untuk ini. Saya suka solusi ini, karena tidak memperkenalkan "hacks" ke solusi.


4
2017-08-19 01:59



OK, didorong oleh jimhark di sini adalah contoh dari pendekatan tabel hash tunggal yang lama: -

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



Trik ini bekerja untukku.

Anda tidak memiliki masalah ini pada server jarak jauh, karena pada server jarak jauh, perintah insert terakhir menunggu hasil dari perintah sebelumnya untuk dieksekusi. Ini tidak terjadi pada server yang sama.

Untunglah situasi itu sebagai solusi.

Jika Anda memiliki izin yang tepat untuk membuat Server Tertaut, lakukan. Buat server yang sama dengan server yang terhubung.

  • di SSMS, masuk ke server Anda
  • pergi ke "Objek Server
  • Klik kanan pada "Linked Server", lalu "New Linked Server"
  • pada dialog, beri nama server tertaut Anda: misalnya: THISSERVER
  • jenis server adalah "Sumber data lain"
  • Penyedia: Penyedia Microsoft OLE DB untuk server SQL
  • Sumber data: IP Anda, bisa juga hanya titik (.), Karena itu localhost
  • Pergi ke tab "Keamanan" dan pilih yang ketiga "Jadilah dibuat menggunakan konteks keamanan saat ini login"
  • Anda dapat mengedit opsi server (tab ke-3) jika Anda mau
  • Tekan OK, server tertaut Anda dibuat

sekarang perintah Sql Anda di SP1 adalah

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

Percayalah, ia bekerja bahkan Anda memiliki insert dinamis di SP2


2
2017-11-22 18:09



Saya memiliki masalah dan kekhawatiran yang sama tentang kode duplikat dalam dua atau lebih sprocs. Saya akhirnya menambahkan atribut tambahan untuk "mode". Ini memungkinkan kode umum ada di dalam satu sproc dan aliran mode yang diarahkan dan rangkaian hasil dari sproc.


1
2018-04-13 20:15



bagaimana dengan hanya menyimpan output ke tabel statis? Seperti

-- 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

tidak ideal, tetapi sangat sederhana dan Anda tidak perlu menulis ulang semuanya.

MEMPERBARUI: solusi sebelumnya tidak berfungsi dengan baik dengan query paralel (async dan multiuser accessing) oleh karena itu sekarang Iam menggunakan temp tables

-- 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

bersarang spGetData konten prosedur yang tersimpan

-- 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



Deklarasikan variabel kursor output ke sp bagian dalam:

@c CURSOR VARYING OUTPUT

Kemudian deklarasikan kursor c ke pilih yang ingin Anda kembalikan. Kemudian buka kursor. Kemudian atur referensi:

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

JANGAN tutup atau realokasi.

Sekarang panggil sp dalam dari luar yang menyediakan parameter kursor seperti:

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

Setelah sp dalam mengeksekusi, Anda @cOUT siap untuk diambil. Loop dan kemudian tutup dan deallocate.


0
2018-05-19 12:31