By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,404 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Stored Proc not working in DTS

P: 9
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. :)
Apr 9 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
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

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.sp_load_pr_monthly
  2. AS
  3.  
  4. declare @dater datetime
  5. set @dater = dbo.usf_BeginMonth(getdate());
  6.  
  7. insert into pr_monthly
  8. select * from pr
  9. where datediff(dd,pr.PROJE_MONTH, @dater) = 0
  10. GO
  11.  
Apr 10 '08 #2

P: 9
Hi,

Thanks for the solution, it worked perfectly! :-)
Apr 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.