Error 3146 - ODBC Call Failed

Error 3146 - ODBC Call Failed

Post by Orissa, Inc » Tue, 09 Dec 1997 04:00:00



I have a VB5 application which updates a SQL table every 3mts
based on entries from a dbase table. I am using SQL server 6.5.
The application uses ODBC to connect to the SQL table  - it has
a Data source and the connect parameter specifies the connection
parameters. The application comes up with Error 3146 after about
15 mts in some cases and it occurs every 15 mts (Here the application
is running in the same machine as the SQL server). Also, in
another test machine (in a workstation), the error happens every 7 hrs
and finally the program freezes.

Has it got anything to do with the Query getting timedout?

I increased the QueryTimeOut parameter in the SQL Enterprise
Manager /Configuration from 0 (Default) to 2 sec. But it doesn't help.

If I increase the QueryTimeOut property of Databse object, will it
override that of the SQL Server ?

Code Example

Dim MySQLDB As Database
Dim SQLBuffData As RecordSet

Do while DbaseData.EOF = False
   Get the first dbase record
   Extract the value of IDSearchBuff from the record
   On Error GoTo SQLTableSelect
   Set SQLBuffData = MySQLDB.OpenRecordset("SELECT Status, EventNo,        
EventStatus, SentDateTime,ConsolEntry FROM OutTransmissions where          
     ConsolEntry = '" & IDSearchBuff & "'", dbOpenDynaset)

  .....
  ......
Loop

It goes thru this procedure couple of times without any problem initially.
The result set of the query always has one record. (There is only one SQL
record corresponding to a Dbase record)

Can someone help?
Thanks in advance.

Santhy

 
 
 

Error 3146 - ODBC Call Failed

Post by Steve Marro » Tue, 09 Dec 1997 04:00:00


Don't change the values from 0 - that means never time out.

Set the QueryTimeOut property of the database object to 0 and you should be
OK

 
 
 

Error 3146 - ODBC Call Failed

Post by Orissa, Inc » Tue, 09 Dec 1997 04:00:00


The QueryTimeOut property of the databse object is 60 sec by default. I
increased
it to 120 sec. And the QueryTimeout value of the SQL Server (Configured in
SQL Enterprise manager) was 0 which I changed to 2 sec. Which of these
will be the actual query timeout value? Which did you mean not to change?

What do you think is the cause of the ODBC error?

Thanks.
Santhy



Quote:> Don't change the values from 0 - that means never time out.

> Set the QueryTimeOut property of the database object to 0 and you should
be
> OK

 
 
 

Error 3146 - ODBC Call Failed

Post by <J.. » Tue, 16 Dec 1997 04:00:00


Make sure you deinitialize the recordset before leaving the sub
Each time you call this line:
Set SQLBuffData = MySQLDB.OpenRecordset("SELECT Status, EventNo,        
 EventStatus, SentDateTime,ConsolEntry FROM OutTransmissions where        

      ConsolEntry = '" & IDSearchBuff & "'", dbOpenDynaset)
a new recordset will be allocated. So, after using the recordset deallocate
it
with:
SET SQLBuffData = Nothing

or

MySQLDB.Recordsets(1).Close
Set MySQLDB.Recordsets(1)=Nothing

(which is nicer....)

/JO



Quote:> I have a VB5 application which updates a SQL table every 3mts
> based on entries from a dbase table. I am using SQL server 6.5.
> The application uses ODBC to connect to the SQL table  - it has
> a Data source and the connect parameter specifies the connection
> parameters. The application comes up with Error 3146 after about
> 15 mts in some cases and it occurs every 15 mts (Here the application
> is running in the same machine as the SQL server). Also, in
> another test machine (in a workstation), the error happens every 7 hrs
> and finally the program freezes.

> Has it got anything to do with the Query getting timedout?

> I increased the QueryTimeOut parameter in the SQL Enterprise
> Manager /Configuration from 0 (Default) to 2 sec. But it doesn't help.

> If I increase the QueryTimeOut property of Databse object, will it
> override that of the SQL Server ?

> Code Example

> Dim MySQLDB As Database
> Dim SQLBuffData As RecordSet

> Do while DbaseData.EOF = False
>    Get the first dbase record
>    Extract the value of IDSearchBuff from the record
>    On Error GoTo SQLTableSelect
>    Set SQLBuffData = MySQLDB.OpenRecordset("SELECT Status, EventNo,      
> EventStatus, SentDateTime,ConsolEntry FROM OutTransmissions where        
>      ConsolEntry = '" & IDSearchBuff & "'", dbOpenDynaset)

>   .....
>   ......
> Loop

> It goes thru this procedure couple of times without any problem
initially.
> The result set of the query always has one record. (There is only one SQL
> record corresponding to a Dbase record)

> Can someone help?
> Thanks in advance.

> Santhy

 
 
 

Error 3146 - ODBC Call Failed

Post by Santh » Wed, 17 Dec 1997 04:00:00


Thanks for the suggestion. Not closing the recordsets would have
 or may have caused other problems.

It looks like I had the 'Generate stored proc for prepared stmts '
option in ODBC32 driver set up dialog turned on. So, every
Query I do was generating a temporary stored proc in tempdb.
And the tempdb size in the workstation that had problems was
1 MB. So, after a while the tempdb was getting filled and
I was getting the error 'Jet engine Error 3146 - ODBC call failed'.

Now with the option turned off, the queries are executed at
run time and my tempdb does not get filled.

Santhy


Quote:> Make sure you deinitialize the recordset before leaving the sub
> Each time you call this line:
> Set SQLBuffData = MySQLDB.OpenRecordset("SELECT Status, EventNo,        
>  EventStatus, SentDateTime,ConsolEntry FROM OutTransmissions where      

>       ConsolEntry = '" & IDSearchBuff & "'", dbOpenDynaset)
> a new recordset will be allocated. So, after using the recordset
deallocate
> it
> with:
> SET SQLBuffData = Nothing

> or

> MySQLDB.Recordsets(1).Close
> Set MySQLDB.Recordsets(1)=Nothing

> (which is nicer....)

> /JO