我的應(yīng)用程序的一些背景。我正在使用C#開發(fā)File Watcher Windows 服務(wù)，該服務(wù)在特定文件夾中查找.bak文件，然后使用它來恢復(fù)該文件所屬的數(shù)據(jù)庫?；謴?fù)的數(shù)據(jù)庫有一個(gè)存儲(chǔ)過程，它調(diào)用 10 個(gè)不同的存儲(chǔ)過程。在還原完成后執(zhí)行存儲(chǔ)過程是文件觀察器的功能。存儲(chǔ)過程是[1_IMPORT_DATA_AND_PROCESS_ALL]在其自身內(nèi)部調(diào)用 10 個(gè)不同的存儲(chǔ)過程。這是還原完成后正在執(zhí)行存儲(chǔ)過程的方法。// 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); }問題我只是從第一個(gè)存儲(chǔ)過程中取回消息輸出，而不是從如下所示[1_IMPORT_DATA_AND_PROCESS_ALL]的從內(nèi)部調(diào)用的存儲(chǔ)過程中取回消息輸出。[1_IMPORT_DATA_AND_PROCESS_ALL]
1 回答
慕絲7291255
TA貢獻(xiàn)1859條經(jīng)驗(yàn) 獲得超6個(gè)贊
再會(huì)，
筆記！此消息未標(biāo)記為“社區(qū) wiki”，因此它是由特定人員以他的名義撰寫的，這不是共享文章。如果您有評(píng)論，請(qǐng)使用評(píng)論而不是更改 OP 打算提供的內(nèi)容（例如內(nèi)容中的額外學(xué)習(xí)點(diǎn)）。謝謝！
在下面的腳本中，我給出了一個(gè)處理嵌套存儲(chǔ)過程錯(cuò)誤的例子。基本思想是使用TRY/CATCH來防止引發(fā)錯(cuò)誤并停止事務(wù)，并使用OUTPUT將錯(cuò)誤信息返回給上層SP
這只是一個(gè)基本的例子......
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 關(guān)注
- 93 瀏覽
添加回答
舉報(bào)
0/150
提交
取消