Hi All
I found an error ( run time error 3669. Execution
cancelled) when ran the following query via VB6 SP5.
select crc,month,sum(v) as v
from ( select crc,v,
case when fadate between '1/1/2003'
and '1/31/2003' then 1
else 2 end as month
from x
where year = '2003' and acc = '99020020') as t
group by crc,month
When the same query was run directly under query analyser
on the server, I did not find any problem. It produced
120 rows. The execution time was arround 100 seconds.
The table x contain 5 million rows.
Then I tried to reduce the number of rows in the table x
to be one thousand rows by modifying the query :
select crc,month,sum(v) as v
from ( select top 1000 crc,v,
case when fadate between '1/1/2003'
and '1/31/2003' then 1
else 2 end as month
from x
where year = '2003' and acc = '99020020') as t
group by crc,month
The new query version above could be run normally via VB.
Note :
The libray I use is Microsoft DAO 3.6
The connecion setting :
DBEngine.DefaultType = dbUseODBC
x = "ODBC;DRIVER=SQL server; UID=xx; PWD=aa;
SERVER=SRV2003; DATABASE=abc"
Set cn = OpenConnection("", dbDriverNoPrompt, False, x)
The record set setting when the error appeared :
Set rs = cn.OpenRecordset(sql, dbOpenDynaset)
Where sql contains query above.
Please help me
Thanks in advance
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!