x

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.

more ▼

asked Feb 25, 2015 at 11:37 AM in Default

avatar image

sand143eep
921 40 50 56

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 25, 2015 at 12:11 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2198
x2031
x443

asked: Feb 25, 2015 at 11:37 AM

Seen: 161 times

Last Updated: Feb 25, 2015 at 12:11 PM

Copyright 2018 Redgate Software. Privacy Policy