Unable to complete running sql package due to function error

rkhorkp

I created a package and when I ran it it gave me the following error:-

An insufficient number of arguments were supplied for the procedure
or function.

When I ran the function in the database, it ran fine.

Any idea what is wrong.

Here is the function:

/*
This is used on the PM Schedules Look Ahead Report in the PM Section of the Report Manager.
It will return PM's that will be due in a specified date range.*/
CREATE FUNCTION dbo.fun_rpt_pm_load_items
(@clnt_fk int,@Sdate datetime,@Edate datetime)
RETURNS @retTable table
(
pk int identity(1,1)
, pm_pk int
, clnt_fk int
, item_type varchar(256)
, tag_no varchar(256)
, task_desc varchar(256)
, job_code varchar(35)
, job_type varchar(60)
, job_desc varchar(256)
, pm_next_date datetime
, my_interval varchar(256)
, interval varchar(256)
, hours float(8)
, rc_code varchar(20)
)
AS BEGIN

--SELECT @sdate=YEAR(@sdate)

DECLARE @pm_temp TABLE (
pm_pk int
, clnt_fk int
, my_ceiling int
, my_interval int
, my_switch varchar(256)
, item_type varchar(256)
, item_desc varchar(256)
, tag_no varchar(256)
, task_desc varchar(256)
, job_code varchar(35)
, job_type varchar(60)
, job_desc varchar(256)
, pm_next_date datetime
, interval varchar(256)
, hours float(8)
, sort_order int
, est_time float
, rc_code varchar(20)
)

/***
Put the insert variables here
***/

DECLARE @pm_pk int
, @my_ceiling int
, @my_interval int
, @my_switch varchar(256)
, @item_type varchar(256)
, @item_desc varchar(256)
, @tag_no varchar(256)
, @task_desc varchar(256)
, @curr_pm_date datetime
, @job_code varchar(35)
, @job_type varchar(60)
, @job_desc varchar(256)
, @pm_next_date datetime
, @interval varchar(256)
, @hours float(8)
, @sort_order int
, @est_time float
, @rc_code varchar(20)

DECLARE @counter int, @ceiling int, @mycounter int

INSERT INTO @pm_temp

SELECT p.pm_pk
,p.pm_clnt_fk
, CASE
WHEN pm_interval IN (0,NULL) THEN 0
WHEN pm_units_identi='U' THEN 1
WHEN pm_units_identi IS NULL THEN 0
WHEN pm_units_identi='D' THEN 365
WHEN pm_units_identi='W' THEN 52
WHEN pm_units_identi='M' THEN 12
WHEN pm_units_identi='Y' THEN 1
ELSE 0
END
,pm_interval
,pm_units_identi
,dbo.fun_rpt_pm_item_details(p.pm_pk, 'item_type')
,''
,dbo.fun_rpt_pm_item_details(p.pm_pk, 'tag_no')
,dbo.fun_rpt_pm_item_details(p.pm_pk, 'tag_desc')
,j.fo_job_code
,t.fj_job_type
,j.fo_job_descrip
,p.pm_next_date
,CASE
WHEN pm_units_identi='D' THEN 'Days'
WHEN pm_units_identi='W' THEN 'Weeks'
WHEN pm_units_identi='M' THEN 'Months'
WHEN pm_units_identi='Y' THEN 'Years'
WHEN pm_units_identi='U' THEN 'Metered'
ELSE '?'
END
,j.fo_time_std
,CASE pm_units_identi
WHEN 'D' Then 1
WHEN 'W' Then 2
WHEN 'M' Then 3
WHEN 'Y' Then 4
WHEN 'U' Then 5
ELSE '0'
END
,p.pm_est_time
,r.rc_code
FROM f_pm_schedules p WITH (NOLOCK) INNER JOIN f_job_library j WITH (NOLOCK) on p.pm_fo_fk=j.fo_pk
INNER JOIN f_job_types t WITH (NOLOCK) on j.fo_fj_fk=t.fj_pk INNER JOIN f_repair_center r WITH (NOLOCK) on p.pm_rc_fk=r.rc_pk
WHERE pm_clnt_fk=@clnt_fk AND pm_units_identi<>'U'
AND pm_clnt_fk=@clnt_fk

DECLARE @pm2 table
(pm_pk int
, clnt_fk int
, my_ceiling int
, my_interval int
, my_switch varchar(256)
, item_type varchar(256)
, item_desc varchar(256)
, tag_no varchar(256)
, task_desc varchar(256)
, job_code varchar(35)
, job_type varchar(60)
, job_desc varchar(256)
, pm_next_date datetime
, interval varchar(256)
, hours float(8)
, sort_order int
, est_time float
, rc_code varchar(20))
INSERT INTO @pm2 SELECT t1.* FROM @pm_temp t1


DECLARE c1 CURSOR
READ_ONLY
FOR SELECT t1.* FROM @pm_temp t1

OPEN c1

FETCH NEXT FROM c1 INTO
@pm_pk
, @clnt_fk
, @my_ceiling
, @my_interval
, @my_switch
, @item_type
, @item_desc
, @tag_no
, @task_desc
, @job_code
, @job_type
, @job_desc
, @pm_next_date
, @interval
, @hours
, @sort_order
, @est_time
, @rc_code
@fetch_status <> -1)
BEGIN
@fetch_status <> -2)
BEGIN

SELECT @counter=1
WHILE @counter&lt;@my_ceiling
BEGIN
INSERT INTO @pm2
VALUES(
@pm_pk
, @clnt_fk
, @my_ceiling
, @my_interval
, @my_switch
, @item_type
, @item_desc
, @tag_no
, @task_desc
, @job_code
, @job_type
, @job_desc
, CASE @my_switch
WHEN 'D' THEN DATEADD(DAY,@my_interval*@counter,@pm_next_date)
WHEN 'W' THEN DATEADD(WEEK,@my_interval*@counter,@pm_next_date)
WHEN 'M' THEN DATEADD(MONTH,@my_interval*@counter,@pm_next_date)
WHEN 'Y' THEN DATEADD(YEAR,@my_interval*@counter,@pm_next_date)
ELSE NULL
END
, @interval
, @hours
, @sort_order
, @est_time
, @rc_code
)

SELECT @counter=@counter+1
END
END
FETCH NEXT FROM c1 INTO
@pm_pk
, @clnt_fk
, @my_ceiling
, @my_interval
, @my_switch
, @item_type
, @item_desc
, @tag_no
, @task_desc
, @job_code
, @job_type
, @job_desc
, @pm_next_date
, @interval
, @hours
, @sort_order
, @est_time
, @rc_code
END

CLOSE c1
DEALLOCATE c1

INSERT INTO @retTable
SELECT
pm_pk
, clnt_fk
, item_type
, tag_no
, task_desc
, job_code
, job_type
, job_desc
, pm_next_date
, my_interval
, interval
, est_time
, rc_code
FROM @pm2
WHERE PM_NEXT_DATE BETWEEN @Sdate and @Edate




RETURN
END

Brian Donahue

Hello,

I don't think that there is anything wrong with the function itself, but it seems as if something is calling the function that is possibly out of date. It could be a trigger that needs to be updated. Maybe using the data option 'disable triggers' will help.

If not, try saving the schema and data scripts when you create the package and run them in Query Analyzer. Then you can easily identify exactly what line of SQL is causing the error.

rkhorkp

Thank you for your reply. Found out that it was one of the other function that was calling this function that was not passing in the correct amount of parameters. Thank you, that help.