Incidents cannot be resolved with error message referencing SLAGetProcessToComplete stored procedure.

book

Article ID: 163187

calendar_today

Updated On:

Products

ServiceDesk

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:

  1. In SQL Management Studio navigate to ServiceDesk Database > Programmability > Stored Procedures
  2. 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