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!

anita hery wrote:
> *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.examnotes.net ***
> Don't just participate in USENET...get rewarded for it! *


Anita is already dead, i'm sure, but i answer as i have not found an
clue on the net and have to make my mind on this problem during
little time.

Error 3669 means the execution cancel for a reason... and you figur
out that this reason could be linked to the number of rows. It als
could come from a bad response time from your data base. Both of thes
problems can be solved by using the ODBC_Timeout properties of you
Querydef Object, as following :
(Dim Qdf as QueryDef)

Set Qdf = cn.CreateQueryDef("")
With Qdf
'---- Increase the following number, it is 30 seconds by default ----
.ODBCTimeout = 120
.Sql = x
Set rs = .OpenRecordset
End With

Well, RIP Anita, and good luck for the poor ones who will read that no


-
cptgoodnigh
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message54305.htm