我的應用程序的一些背景。我正在使用C#開發File Watcher Windows 服務，該服務在特定文件夾中查找.bak文件，然后使用它來恢復該文件所屬的數據庫?；謴偷臄祿煊幸粋€存儲過程，它調用 10 個不同的存儲過程。在還原完成后執行存儲過程是文件觀察器的功能。存儲過程是[1_IMPORT_DATA_AND_PROCESS_ALL]在其自身內部調用 10 個不同的存儲過程。這是還原完成后正在執行存儲過程的方法。// Trigger Stored Procedure after restore. private void triggerSP(String connectionStr){ // This doesn't open the Connection. conn.Open() has to be explicitly called. SqlConnection conn = new SqlConnection(connectionStr); try { conn.Open(); conn.FireInfoMessageEventOnUserErrors = true; // Capture messages returned by SQL Server. conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e) { message += " -> " + e.Message + " -> "; }; //conn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage); //.create a command object identifying the stored procedure. SqlCommand cmd = new SqlCommand("[dbo].[1_IMPORT_DATA_AND_PROCESS_ALL]", conn); cmd.CommandTimeout = 0; // 2. set the command object so it knows to execute a stored procedure. cmd.CommandType = CommandType.StoredProcedure; // Add a check here as well. // execute the command. SqlDataReader rdr = cmd.ExecuteReader(); string[] info = new string[] { "Message: \n" + message }; WriteToFile(info); // Since we are not using - using block we have to explicitly call Close() to close the connection. conn.Close(); } catch (SqlException SqlEx){ string[] error = new string[3] ; string msg1 = "Errors Count:" + SqlEx.Errors.Count; string msg2 = null; foreach (SqlError myError in SqlEx.Errors) msg2 += myError.Number + " - " + myError.Message + "/" ; conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e) { message += "\n" + e.Message; }; error[0] = msg1; error[1] = msg2; error[2] = message; WriteToFile(error); }問題我只是從第一個存儲過程中取回消息輸出，而不是從如下所示[1_IMPORT_DATA_AND_PROCESS_ALL]的從內部調用的存儲過程中取回消息輸出。[1_IMPORT_DATA_AND_PROCESS_ALL]
1 回答
慕絲7291255
TA貢獻1859條經驗 獲得超6個贊
再會，
筆記！此消息未標記為“社區 wiki”，因此它是由特定人員以他的名義撰寫的，這不是共享文章。如果您有評論，請使用評論而不是更改 OP 打算提供的內容（例如內容中的額外學習點）。謝謝！
在下面的腳本中，我給出了一個處理嵌套存儲過程錯誤的例子?；舅枷胧鞘褂肨RY/CATCH來防止引發錯誤并停止事務，并使用OUTPUT將錯誤信息返回給上層SP
這只是一個基本的例子......
CREATE or ALTER PROCEDURE L1 (
@InputInt int,
@ErrMessage NVARCHAR(MAX) OUTPUT,
@ErrNum INT OUTPUT
)AS
SELECT @@NESTLEVEL AS 'Inner Level'; -- this information present the level of the SP during the execution. It is not needed for the solution but for the sake of the learning and understanding of nested SP
Select 'Start L1'
BEGIN TRY
-- When the ionput is 0 we Generate a divide-by-zero error.
SELECT 1/@InputInt;
END TRY
BEGIN CATCH
SET @ErrMessage = ERROR_MESSAGE()
SELECT @ErrMessage
END CATCH;
SET @ErrNum = @@ERROR
IF (@ErrNum > 0) Begin
SELECT 'L1 error Number: ' + CONVERT(NVARCHAR(10), @ErrNum)
Return
END
ELSE
select 'L1 OK'
GO
CREATE or ALTER PROCEDURE L2 (
@InputInt int
) AS
Declare @ErrMessage NVARCHAR(MAX) = '', @ErrNum INT = 0
SELECT @@NESTLEVEL AS 'Outer Level';
BEGIN TRY
EXEC L1 @InputInt, @ErrMessage, @ErrNum;
END TRY
BEGIN CATCH
SELECT 'There was error!'
select @@ERROR
END CATCH
GO
EXECUTE L2 1 -- OK
GO
EXECUTE L2 0; --Raise error in the nested stored procedures
GO
分享
編輯
跟隨
- 1 回答
- 0 關注
- 95 瀏覽
添加回答
舉報
0/150
提交
取消