SQL 存储过程每 25 分钟抛出一次错误 [英] SQL Stored Procedure Throws Error every 25 minutes

问题描述

我有一个存储过程，每 25 分钟就会抛出一个错误.我在应用程序中有代码，当它失败时会向我发送电子邮件.我试图确定是否是程序本身导致了问题，或者它是否可能与服务器上发生的其他事情发生冲突，或者应用程序中的代码是否可能出错.直到本周它都运行良好.我们本周对应用程序进行了更改，并向 ADT_Diagnosis 表添加了新代码.自从添加代码以来，它一直在不断地抛出错误.感谢所有提示和建议.提前致谢！

I have a stored procedure that throws an error every 25 minutes. I've got code in the application that sends me an email when it fails. I'm trying to nail down if it is the procedure itself that is causing the issue or if it could be clashing with something else happening on the server or if the code in the application is possibly erroring out. It's worked well until this week. We made a change to the application this week and added new codes to the ADT_Diagnosis table. Since the addition of the codes, it's steadily been throwing an error. All tips and suggestions are appreciated. Thanks in advance!

这是它发送给我的错误:

This is the error that it sends me:

护理床服务发生错误."System.Data.SqlClient.SqlException (0x80131904):超时已过期.操作完成前超时时间已过或服务器未响应.在 System.Data.SqlClient.SqlConnection.OnError(SqlException 异常，Boolean breakConnection) 在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException 异常，Boolean breakConnection) 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 在 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 在 System.Data.SqlClient.SqlDataReader.get_MetaData() 在 System.Data.SqlClient.SqlDataReader.get_MetaData().SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) 在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() 在 Service.MainWindow.UpdateDataFromADT_Codes(SqlConnection SqlConn) 在 Service.MainWindow.TaskThread_DoWork(Object sender, DoWorkEventArgs e)

Error Occured on the Nursing Bed Service. "System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Service.MainWindow.UpdateDataFromADT_Codes(SqlConnection SqlConn) at Service.MainWindow.TaskThread_DoWork(Object sender, DoWorkEventArgs e)

这里是SQL存储过程:

Here is the SQL stored procedure:

ALTER PROCEDURE GetActivePatientCoreMeasures 
   -- Add the parameters for the stored procedure here
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

SELECT DISTINCT ADT.[PV1 Room],adt.[Patient Account Number], 
    [Diagnosis Codes].Description
FROM [CPSISQL].dbo.ADT
LEFT JOIN [CPSISQL].dbo.ADT_Diagnosis 
ON [CPSISQL].dbo.ADT.[Patient Account Number] =
    [CPSISQL].dbo.ADT_Diagnosis.[Patient Account #]
RIGHT JOIN [Diagnosis Codes]
ON ADT_Diagnosis.[Diagnosis Code] = [Diagnosis Codes].Code
WHERE [CPSISQL].dbo.ADT.[PV1 Discharge Date/Time] = ''
AND
(
[CPSISQL].dbo.ADT.[PV1 Department] = '028' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '030' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '032' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '038' OR
[CPSISQL].dbo.ADT.[PV1 Department] = '042' 
)
AND NOT(ADT_Diagnosis.[Diagnosis Type] = 'A')
ORDER BY adt.[PV1 Room]
END

推荐答案

您需要排除连接问题.请参阅 SQL 连接错误:System.Data.SqlClient.SqlException (0x80131904).

You need to rule out connectivity issues. See SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904).

如果这不能解决问题，请从 SSMS (SQL Server Management Studio) 手动运行存储过程，最好在错误发生时立即运行，然后查看恢复所需的时间.VB.NET 中的默认命令超时为 30 秒，因此如果 SSMS 需要更长的时间，则意味着您必须增加超时时间(请参阅 为所有新命令对象设置自定义默认 CommandTimeout)，或使您的存储过程更快.

If that doesn't solve the issue, run the stored procedure manually from SSMS (SQL Server Management Studio), preferably as soon as the error happens, and see how long it takes to come back. The default command timeout in VB.NET is 30 seconds, so if it takes longer from SSMS then it means you have to either increase the timeout (see Set custom default CommandTimeout for all new Command Objects), or make your stored procedure faster.

为了使您的存储过程更快，请从 SSMS 再次运行它，但这次确保启用包括实际执行计划".这将告诉您查询计划中的哪些操作需要更长的时间，甚至可以建议将哪些索引添加到您的表中以提高性能.

To make you stored procedure faster, run it again from SSMS, but this time make sure "Include Actual Execution Plan" is enabled. This will tell you what operations in the query plan are taking longer and can even suggest what indexes to add to your table to improve performance.

这篇关于SQL 存储过程每 25 分钟抛出一次错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！