I am trying to retrieve a rowset using a join from 2 Sybase databases (on the same server - this is not a problem) but when I use T-SQL variables to compare the dates DTS Designer returns no rowset (or hangs) when I preview the results of the query.

Here is an example of my query that works:

select T1.column1,
T1.column2,
T1.column3,
T2.column4,
from sybasedb1.dbo.table1 T2,
sybasedb2.dbo.table2 T1
where
(T1.date BETWEEN '2003-03-02 00:00:00' AND '2003-04-12 00:00:00')
and T2.column4 = T1.column2
group by T1.column1, T1.column2, T1.column3, T1.column4

This query works fine.

I need to have the date range to be dynamic so I have the following code:

declare @last_sunday smalldatetime
declare @next_sunday smalldatetime

select @last_sunday = dateadd(day,1-(datepart(dw,getdate())),getdate()) where datepart(dw,getdate()) != 1
select @last_sunday = dateadd(day, -7, @last_sunday)
select @next_sunday = dateadd(day, 7, @last_sunday)

select @last_sunday = convert(smalldatetime,convert(char(8),@last_sunday , 112))
select @next_sunday = convert(smalldatetime,convert(char(8),@next_sunday , 112))

select T1.column1,
T1.column2,
T1.column3,
T2.column4,
from sybasedb1.dbo.table1 T2,
sybasedb2.dbo.table2 T1
where
(T1.date BETWEEN @last_sunday AND @next_sunday)
and T2.column4 = T1.column2
group by T1.column1, T1.column2, T1.column3, T1.column4

This works through the Sybase sql client like a charm but from DTS returns no rowset in preview and thus i cannot build any transformations.

Any ideas?