Incidents cannot be resolved with error message referencing SLAGetProcessToComplete stored procedure.
Article ID: 163187
Updated On:
Products
Issue/Introduction
Incidents in ServiceDesk 8.0 cannot be resolved. When looking into Log Viewer when trying to resolve an incident, error message says SLAGetProcessToComplete stored procedure cannot be run.
Application Name : ProcessManager
Log Level :Error
Log Category :SlaProcess
Message :
[SLA.DataLayer] Error in GetSlaProcessToComplete. Error executing SQL query.
Client: IP=xxxx::xxxx:xxxx:xxxx:xxxx HostName=xxxx::xxxx:xxxx:xxxx:xxxx Browser=Mozilla/4.0 (compatible; MSIE 6.0; MS Web Services Client Protocol 4.0.30319.18408)
LogicBase.Framework.DataLayer.DataLayerException: Error executing SQL query. ---> System.Data.SqlClient.SqlException: Procedure or function 'SLAGetProcessToComplete' expects parameter '@Status', which was not supplied.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at LogicBase.Framework.DataLayer.DALProxy.ExecuteCommandMultipleRecords(IDbCommand cmd, Type entityType)
--- End of inner exception stack trace ---
at LogicBase.Framework.DataLayer.DALProxy.ExecuteCommandMultipleRecords(IDbCommand cmd, Type entityType)
at LogicBase.Ensemble.SLA.DataLayer.SlaProcess.GetSlaProcessToComplete(String reportLogProcessID, String milestoneID)
Cause
There is an incorrect version of SLAGetProcessToComplete stored procedure in ServiceDesk database.
Resolution
This issue will be fixed in ServiceDesk 8.0 HF2.
Workaround:
Before modifying the stored procedure, please make a backup of the current stored procedure to have a simple way to fall back if this does not fix the problem. We would recommend saving this to a file:
- In SQL Management Studio navigate to ServiceDesk Database > Programmability > Stored Procedures
- Right-click on dbo.SLAGetProcessToComplete and select Script Stored Procedure as > ALTER To > File ...
Now, run the following query against the ServiceDesk database to replace the stored procedure with a correct one.
ALTER procedure [dbo].[SLAGetProcessToComplete]
(
@ReportLogProcessID nvarchar(36),
@SLAMilestoneID nvarchar(36)
)
as
set nocount on
select sp.*
from dbo.SLAProcess sp
where sp.ReportLogProcessID = @ReportLogProcessID and
sp.[Status] not in (4, 5) and
sp.SLAConfigID in (select c.SLAConfigID from dbo.SLAConfig c
where c.SLAMilestoneID = @SLAMilestoneID)
GO
Feedback
