sand143eep avatar image
sand143eep asked

Getting error with stored proc

i am new to TSQL and not able find where the exact problem happening in the code we are getting problem in the application.we are using SQL Server 2005. the error is as follows: Failed to process 'eExecuteSQL' Metastorm engine Database Connector request. ErrorCode: '-2147217833'. Description: 'Arithmetic overflow error converting expression to data type datetime.'. Source: 'Microsoft OLE DB Provider for SQL Server'. SQL State: '22003'. NativeErrorCode: '8115' Procedure SelectSQL call failed. Position: 326 %SelectSQL(exec [dbo].[ICCM_GetNewSLA] N'Islamabad','2015-02-18 09:11:51','2015-02-20 05:00:57','Global','2015-01-26 05:49:20') (%Status:="Open");(%RestartDateTime:=%System.Time);(%Deadline:=%SelectSQL("exec [dbo].[ICCM_GetNewSLA] N'%Replace(%Location,%CHR(39),%CHR(39)%CHR(39),0)','%FormatTime(%StopDateTime,"YYYY-MM-DD HH:NN:SS")','%FormatTime(%RestartDateTime,"YYYY-MM-DD HH:NN:SS")','%ActiveFixSLA[1]','%FormatTime(%Deadline,"YYYY-MM-DD HH:NN:SS")'"));(%Action.Notes:="%Replace(%Action.Notes,%Chr(39),%Chr(39)%Chr(39),0)");(%ExecSQL("INSERT INTO CRM_Time_Taken (EFolderID,TTH,TTM,eUserName,Comments,ActionType) VALUES ('%FolderID','%TmpTimeTakenHours','%TmpTimeTakenMinutes','%User.Name','Clock Restarted','Start The Clock')"));(%StopDateTime:=%Empty());(%RaiseFlag(EscalationUpdate,%FolderID,%FixSLAType,%ResponseSLAType,"Active",%Responded,%FormatTime(%DeadLine,"YYYY-MM-DD HH:NN:SS"),%FormatTime(%ResponseTime,"YYYY-MM-DD HH:NN:SS"),%ActiveFixSLA[1],%ActiveResponseSLA[1],%Location)); the code for the sp present in above error is as follows: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Proc [dbo].[ICCM_GetNewSLA] @Location nvarchar(100), @StopTime datetime, @StartTime datetime, @WeekPattern nvarchar(100), @DeadLine datetime AS BEGIN SET NOCOUNT ON -- Declarations DECLARE @HolidaySet nvarchar(100) DECLARE @Seconds int IF (SELECT count(*) from CRM_Locations where Location=@Location) < 1 BEGIN SET @Location=(Select Location from dbo.CRM_Server) END -- Calculations IF @WeekPattern = '' BEGIN SELECT @WeekPattern=DefaultWorkset from CRM_Server END SELECT @StopTime=dbo.ICCM_LocalTime(@StopTime,@Location), @StartTime=dbo.ICCM_LocalTime(@StartTime,@Location) SELECT @HolidaySet=HolidaySet from CRM_Locations where Location=@Location IF @HolidaySet is null BEGIN SET @HolidaySet = 'Standard' END SELECT @Seconds = [dbo].[ICCM_BusinessHours](@StopTime,@StartTime,@WeekPattern,@HolidaySet) SELECT dbo.ICCM_UniversalTime([dbo].[ICCM_GetDeadline]('Seconds',@Seconds,@WeekPattern,@HolidaySet, dbo.ICCM_LocalTime(@DeadLine,@Location)),@Location) END please suggest which part i need to change to resolve the issue.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Grant Fritchey avatar image
Grant Fritchey answered
Too many variables to know for sure what's going on. For example, this function could be returning a value that isn't a datetime @StopTime=dbo.ICCM_LocalTime(@StopTime,@Location), That's one place I'd look. The error is very explicit. Some number can't be converted to datetime. So, look to all the places where you're attempting to set datetime within the procedure and figure out why that function might have a value that's not right. It's possible that there's an error in the other function.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.