ODBC error help 1

[PappaG]

Hi All

Not sure if this is the correct place to ask this but here goes. i have very little SQL knowledge so bear with me. I have a sql database which i use access to connect to my problem is when running queries against this i keep getting the error message odbc call failed microsoft odbc sql server driver timeout expired (#0). I have loaded the most recent MDAC with the relevant service packs i have also set the query timeout value on the sql box itself to 0 which i believe should be unlimited. The error itself suggests to me it is the odbc driver itself but i cant seem to get round this. Its sql 2000 i am using and i connect through TCP/IP ?

 

[NoCoolHandle]

Can you ping the SQL box from your PC?
Can you connect via a UNC path?
What type of Security are you using?
What Network Librarys are you using? Client and Server....
(yes I know you said tcpip, but what is the setup via the SQL CLient Network tool and the SQL Server Network tool?)
Has anyone added a service pack that adds a firewall to your SQL box?
Are you specifying a port when you connect or is SQL listening to the standard port.?

 

[PappaG]

Thanks for your response NoCoolHandle

Yes i can ping the SQL server i can also query tables but anytime there is a large amount of data i get the timeout.
I can connect via a UNC path but this is not a viable option as this was only opened as a tempory measure and a firewall rule will be reapllied to allow me access on only ports 1433 and 80.
I am using sql authentication
Network libary is TCP/IP both client and server on port 1433 MDAC 2.8 SP1

The error indicated or at least to me it seems to point to the odbc sql driver rather than the SQL box itself. I found an article that suggested this may be to do with the kerbos key used in the authentication and there was a registry hack to use a smaller key but i was unsure if this applied to my specific fault.

 

[NoCoolHandle]

Sorry for taking so long to get back... I was doing some work

Ok.. It hits me that the issue is more in the area of "CommandTimeout"

I don't really know enough about access nowadays.. My last real trip that direction was access 97 so I expect alot has changed...

If you are using linked tables (which is probably the common - but not best way it is done) Look to see if there are any properties you can change for commandtimeout..

If you are using adocode and recordsets.. look at the commandtimeout propertie of either the connection or recordset.. (I cant remember which, but suspect it is the recordset)

Also.. This might be an indication that you have "data fragmentation" this can be fixed by simply "rebuilding" your indexes .. Look at books online for "dbcc dbreindex".. or something liken that.. also "dbcc defragindex" will do almost as good a job, but has less impact than a full index rebuild..

HTH


Rob

 

[PappaG]

Thanks for your help NocoolHandle. Unfortunatly access is about as far as my knowledge goes but yeah your right i am using linked table but can find no referance to timeouts anywhere.
The database does not belong to us it belongs to our telco so i think i will pass this over to them to check for fragmentation as if anything goes wrong if i rebuild it im sure i will be strung up by the short and curlies.

Thanks again

 

[NoCoolHandle]

What version of Access and SQL are you using... I might be able to reproduce it where I am as I have just about every version of each and it shouldn't take long to test.


Rob

 

[Burglar]

If you are using standard Access query objects then:

In the properties under view in the query design, set the odbc timeout property to 0...the default is 60 seconds.

Otherwise, check the ado/dao timeout setting in the connection string if that is the route you are taking.

Burglar

 

[PappaG]

Thanks for all your help guys

Burglar you hit the nail on the head i though i had been through every option in access to try and find a timeout value. Exactly what i was looking for.

Cheers