Stored Proc not working in DTS

[JinxT]

Hi,

I am trying to execute a stored procedure within a DTS. The sp works fine from query analyser and inserts all rows as it should, but when exec in DTS the insert does not occur and the table remains empty.

The stored procedure is as follows:

CREATE PROCEDURE dbo.sp_load_pr_monthly
AS

declare @dater datetime
set @dater = dbo.usf_BeginMonth(getdate());

insert into pr_monthly
select * from pr
where pr.PROJE_MONTH = @dater
GO

The function used:

--determines first day of preceeding input month

CREATE function dbo.usf_BeginMonth (@indate datetime)
returns datetime
begin

declare
@start datetime,
@temp datetime

select @start= cast(year(dateadd(M, -1, @indate)) as varchar(4)) + '-01-' + cast(month(dateadd(M, -1, @indate)) as varchar(2))
return @start

end

Any suggestions as to why this is would be great!

Thanks. :)

[deepuv04]

Hi,

I am trying to execute a stored procedure within a DTS. The sp works fine from query analyser and inserts all rows as it should, but when exec in DTS the insert does not occur and the table remains empty.

The stored procedure is as follows:

CREATE PROCEDURE dbo.sp_load_pr_monthly
AS

declare @dater datetime
set @dater = dbo.usf_BeginMonth(getdate());

insert into pr_monthly
select * from pr
where pr.PROJE_MONTH = @dater
GO

The function used:

--determines first day of preceeding input month

CREATE function dbo.usf_BeginMonth (@indate datetime)
returns datetime
begin

declare
@start datetime,
@temp datetime

select @start= cast(year(dateadd(M, -1, @indate)) as varchar(4)) + '-01-' + cast(month(dateadd(M, -1, @indate)) as varchar(2))
return @start

end

Any suggestions as to why this is would be great!

Thanks. :)

Hi,
I think the date comparison in the select statement used in the procedure is failed because of no timestamp so try change the procedure as follows and try

CREATE PROCEDURE dbo.sp_load_pr_monthly
AS
 
declare @dater datetime
set @dater = dbo.usf_BeginMonth(getdate());
 
insert into pr_monthly
select * from pr
where datediff(dd,pr.PROJE_MONTH, @dater) = 0
GO
 

[JinxT]

Hi,

Thanks for the solution, it worked perfectly! :-)