Issue running a stored procedure within a DTS

• johnh298

  SSC-Addicted

  Points: 466

  More actions

  May 17, 2007 at 10:34 am

  #120141

  Hi all, i am probably missing something very simple but i am running into issues when running a stored procedure within a DTS passing it a parameter. If i run the SP in query analyser it ecxecutes without issue. However when i run it in the DTS it stops after creating the first 3 records.

  This is what is executing in the DTS SQL task object, the ? for the parameter is being filled by a date global variable although i have also tried it as a string -

  exec dbo.usp_UpdateQuarterTable '2000-01-01', '2010-12-31',?

  The SP is copied below (appologies for any coding issues but it does work, well normally anyway!) If anyone could give me a pointer as i cannot seem to find any info anywhere else!

  Also worth noting when it creates the 3 records it is calculating the correct values so the 3rd parameter is coming through correctly in some fashion.

  Many thanks

  John

  CREATE PROCEDURE dbo.usp_UpdateQuarterTable

  @sdate datetime, -- Start date

  @edate datetime, -- End date

  @cqtrdate datetime-- Date of current quarter to set base for current quarter flag

  AS

  -- Store procedure to create quarter records

  truncate table tbMLAR_Quarter

  declare @date datetime

  declare @iqtr int-- integer format quarter of current date

  declare @sqtr char(1)-- string format quarter of current date

  declare @qtr int-- Qtr of previous date

  set @date = @sdate

  set @qtr = 0

  while @date <= @edate -- Loop until enddate passed
  begin
  select @iqtr = datepart(qq, @date)
  select @sqtr = convert(char(1), datepart(qq, @date))
  if @qtr <> @iqtr -- If quarter of current date is different to quarter of previous date then new quarter

  begin-- Add record for new quarter

  select @cqtrdate, @sqtr, @date

  insert into tbMLAR_Quarter

  values(convert(varchar(6), datepart(yy, @date)) + 'Q' + @sqtr, -- Quarter Name

  @date,-- Quarter Start Date

  dateadd(mm, 3, @date),-- Quarter End date

  datediff(qq, @cqtrdate, @date))-- Current quarter flag

  end

  select @qtr = @iqtr--

  select @date = @date + 1

  end

  GO

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  May 21, 2007 at 5:50 am

  #707778

  Hi,

  I'm wondering if it's to do with the SELECT @date = @date + 1 line not functioning as required. If you change it to DATEADD(dd, 1, @date) and try that(?!?)

  Also I notice that your end dates are the 1st of each start month of the next quarter you might want to change the Quarter End Date to DATEADD(dd, -1, dateadd(mm, 3, @date)) to make it the last day of the last day of each 3rd month.

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]