Intermittent udf Problem

[paulkeys]

Hi all,

I'm using SQL Server 2005 and am currently experiencing an intermittent problem when trying to run a udf.

The udf accepts 2 dates as parameters, and calculates the work time between based on working days, bank holidays etc.

The problem i have is that sometimes calling the function will fail with 'incorrect syntax near m' error - although there is no table name or alias m in the function or database.

Restarting the SQL Server service will solve the issue, but unfortunately that's not really a good enough solution, as this function is also used within a several SSIS packages that run frequently during the day.

Has anyone ever experienced this problem? Any help would be most appreciated.

 

[bborissov]

It would be great if you post the function code and how you call it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[paulkeys]

Thanks for the reply - i've posted the function below. It's called in a couple of ways;
1. Update statement within SSIS Package - UPDATE table1 SET field1 = dbo.udf_job_time(date1,date2,code)
2. Within views - select dbo.udf_job_time(date1,date2,code) as field1

Thanks in advance

_____

CREATE function [dbo].[udf_job_time] (@start_date datetime, @end_date datetime, @calendar_code varchar (10))
RETURNS numeric
AS
BEGIN
declare @table_counter int --used to popultate temp table with pseudo rowid
declare @start datetime --start time of job i.e. created time
declare @work_start datetime -- start of working day on the date the sr was created
declare @start_day varchar (10) --used to hold start day name for look up to calendar tables
declare @work_end datetime -- end of working day on the date the job completed
declare @end_day varchar (10) --used to hold end day name for look up to calendar tables
declare @cal_id varchar (10) -- used to hold the calendar id for use in look up to calendar tables
declare @end datetime --used to hold time engineer arrived on site to do work
declare @date_to_insert datetime --used to hold each day and increment them from first to last day
declare @time_taken numeric -- used to hold total time taken for return in function
declare @bad_date1 int --used to check if 1st date passed in is a valid date and if not go to end and return null
declare @bad_date2 int --used to check if 2nd date passed in is a valid date and if not go to end and return null
declare @days table (
all_dates datetime,
day_of_week varchar (10),
holiday varchar (1),
row int,
record_start_time datetime,
record_end_time datetime,
time_taken int) -- table variable to hold all calculated information

set @bad_date1 = isdate(@start_date)
if @bad_date1 = 0 goto error

set @bad_date2 = isdate(@end_date)
if @bad_date2 = 0 goto error

if @start_date >= @end_date
goto error



set @start = @start_date
set @end = @end_date
set @cal_id = @calendar_code
set @date_to_insert = @start
set @start_day = (select datename(weekday,@start))
set @work_start = (select convert(varchar,start_tm,108) from CALENDAR_WORKING_HOURS
where end_day = @start_day and SCHED_CAL_ID = @cal_id)
set @end_day = (select datename(weekday,@end))
set @work_end = (select convert(varchar,end_tm,108) from CALENDAR_WORKING_HOURS
where end_day = @end_day and SCHED_CAL_ID = @cal_id)
set @table_counter = 1


/* Populate table variable with all the days in the start - end range
with the name of the day (for lookup to calendar tables) and a pseudo rowid */
while convert(varchar,@start,112) <= convert(varchar,@end,112)
begin
insert into @days (all_dates,day_of_week,row)
values (@date_to_insert,datename(weekday,@date_to_insert),@table_counter)
set @date_to_insert = @date_to_insert + 1
set @table_counter = @table_counter + 1
IF convert(varchar,@date_to_insert,112) > convert(varchar,@end,112)
BREAK
ELSE
CONTINUE
END

/* Update the table variable to show which of the days in the table
are holidays - uses calebdar id variable for lookup to calendar tables*/
update @days
set holiday = (
select case
when substring(convert(varchar,all_dates,120),1,10) in (
select substring(convert(varchar,start_dt,120),1,10) from
calendar_hol_days a join
calendar_hol b
on a.excpt_cal_id = b.row_id
join calendar c
on b.row_id = c.excpt_cal_id
where c.row_id = @cal_id) then 'Y'
else 'N'
end)

/* Update table variable with the start and end times of days not at
beginning and end of date ranges - uses day name and calendar id
variable for look up to calendar tables */
Update @days
set record_start_time = (
select START_TM from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id),
record_end_time = (
select END_TM from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id)
where row > (select min(row) from @days)
and row < (select max(row) from @days)

/* Check to see if job start time is greater than the working day start time
for the first day and if so update table variable to show job start time
if not update table to show working day start time for first day */
if convert(varchar,@start,108) > convert(varchar,@work_start,108)-- compare time portions only
update @days set record_start_time = @start where row = (select min(row) from @days)
else
update @days set record_start_time = @work_start where row = (select min(row) from @days)

/* Update table variable to set first day end time to be the end of the working day*/
Update @days
set record_end_time = (select END_TM from CALENDAR_WORKING_HOURS

where end_day = day_of_week AND SCHED_CAL_ID = @cal_id)
where row = (select min(row) from @days)

/* If table variable has more than one row set last day start time
to be the start of the working day if only one row ignore update*/
If (select count (*) from @days) = 1
GOTO miss_step
else
Update @days
set record_start_time = (select start_tm from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id)
where row = (select max(row) from @days)

miss_step:

/* Update table variable to set last day end time to be the end of the job
unless the job finished after the end of the working day where end of working
day is used*/
if convert(varchar,@end,108) > convert(varchar,@work_end,108)-- compare time portions only
Update @days
set record_end_time = @work_end
where row = (select max(row) from @days)
else
Update @days
set record_end_time = @end
where row = (select max(row) from @days)

/* Update table variable to calculate time difference from start to end of
day for all days involved */
update @days
set time_taken =
datediff(minute,convert(varchar,record_start_time,108),convert(varchar,record_end_time,108))

/*set output variable to be the sum of all times where not a
holiday and time not null (weekends) */
set @time_taken = (select sum(time_taken) Job_Time
from @days
where holiday = 'N'
and time_taken is not null)
if @time_taken < 0
set @time_taken = 0

RETURN (@time_taken)

error:
RETURN (NULL)
END

 

[bborissov]

At first glance I don't see anything that could cause such error. Maybe somebody else could find what could cause this.
Did you get on what row of this functions you get the error?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

check the table you are running it against, is there a trigger? MAybe sometimes you are hitting a bad part of the trigger which has never been debugged.

Questions about posting. See faq183-874

 

[paulkeys]

Hi - thanks for the reply.

There aren't any triggers present - any other suggestions most appreciated