DTS Sybase views to SQL Server with Dynamic SQL query

[HulaGirl]

Hi, I have DTS package with the following statements in the source SQl query window on Source tab. I am importing data from Sybase view to corresponding tables in SQL Server 2000. I can parse the query. but preview return this DTS Designer Error message "No rowset was returned from the SQL statement".

it appears that only part of the query was executed here. does anyone has an idea on what is going on / wrong here? thanks so much in advance.

here is query:
##########################################################

declare @sufix varchar(200)

select @sufix = (select substring(convert(varchar(20), dateadd(day,-10,getdate()),100),8,4)+
substring(convert(varchar(20), dateadd(day,-10,getdate()),101),1,2)+
substring(convert(varchar(20), dateadd(day,-10,getdate()),106),1,2))

execute ('select * from eCallByCallStat'+@sufix)
##########################################################

 

[gmmastros]

You can replace

declare @sufix varchar(200)

[tt][blue]select @sufix = (select substring(convert(varchar(20), dateadd(day,-10,getdate()),100),8,4)+
substring(convert(varchar(20), dateadd(day,-10,getdate()),101),1,2)+
substring(convert(varchar(20), dateadd(day,-10,getdate()),106),1,2))[/blue][/tt]

With:

[!]Select @sufix = Convert(VarChar(8), GetDate()-10, 112)[/!]

I'm not sure why the DTS package isn't working though.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom