ODBC error - some users only

[Kawa]

Hi all,

Hoping you can help. We have circa 12 users accessing SQL Server 2005 (SBS Server 2003) from Windows XP / Access 2007 front ends. The system has been live for 12 or 13 years with very little issue. 60 odd MsSQL tables and a good number of views.

Application will update one particular table using DoCmd.RunSQL. For the last couple of years, we would occasionally will receive a ODBC Timeout error when updating this one table.

All other table updates and views etc will be fine, just this one 'problem' table.

A SQL restart would normally fix this problem, but in the last couple of months, the frequency has increased and restarting the db would always fix the problem.

Updated access frontend and sql backend to use Stored Procedures and pass thru queries and we thought we had solved the problem, but alas the next day we began to receive ODBC FAILED errors. Restarting the db would not always solve the issue.

All users access MsSQL using the same SQL username. Have tried differing combinations of network group permissions and using direct SQL usernames.

All clients use SQL SERVER odbc connector. Have also downloaded SQL Native Client odbc to an XP machine, but this did not resolve the issue either.

Strange thing is - if someone else logs onto the same local machine, they can perform the update. This user also happens to be a server admin.


If I log into the (SBS) server remotely and run the application from the server desktop, the update also works fine also.

If I then make a 'problem' user a server admin, the update on the 'problem' table works fine.

What has come to light today is the server people did driver update on the server a few weeks ago - which apparently included a SQL driver update...Not 100% sure on this though...

Question is, could some memory allocation settings or similar have been changed as part of the update? It's all very odd

 

[Rx_]

telnet <serverName> 1433
I have seen the PORT 1433 change to dynamic and cause intermittent problems like this one.
If it reports an error there is an underlying network connectivity problem between the desktop and the server.

If it gives a blank screen the netowkr connectivity works, and further information about your environment and the error may be needed.

In another situation, refreshing, restarting sometimes worked. This error would come up typically during a new record or update.
There was a corruption in the SQL Server table. Backing up and restoring fixed it.

 

[mdlueck]

What sort of posture is your FE database configuration in? Is each workstation using its own FE DB or is it shared on a file server? Each workstation should be using its own copy of the FE DB, on the local drive.

Have you ever decompiled the FE DB?

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB

gggrrr... seems the Wiki has not been fixed yet. Old post here:
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

 

[Rx_]

Thanks for that. I totally forgot to ask if it was running this from a file server.

 

[Kawa]

Hi, thanks for your reply.

What sort of posture is your FE database configuration in? Is each workstation using its own FE DB or is it shared on a file server? Each workstation should be using its own copy of the FE DB, on the local drive.

Each client machine has a copy of the FE on their desktop. FE uses linked tables via odbc user DSN to MsSQL on a network server.

Have you ever decompiled the FE DB?

Sounds interesting, what is the purpose of Decompile / Compact / Compile? I do note that a couple of years ago the FE increased significantly in size, but none of the local temp tables have any significant data. Would this procedure help?

 

[Kawa]

telnet <serverName> 1433
I have seen the PORT 1433 change to dynamic and cause intermittent problems like this one.
If it reports an error there is an underlying network connectivity problem between the desktop and the server.

If it gives a blank screen the netowkr connectivity works, and further information about your environment and the error may be needed.

Interesting point and something that they are exploring is upgrading the network switches to 1gib connections. I don't think network is the issue at files on the server are still accessible along with other DB tables.

In another situation, refreshing, restarting sometimes worked. This error would come up typically during a new record or update.
There was a corruption in the SQL Server table. Backing up and restoring fixed it.

Thanks for this, will pass the info on

 

[Kawa]

We're also looking at testing memory on the SQL server and the problem seems very erratic.

 

[mdlueck]

Sounds interesting, what is the purpose of Decompile / Compact / Compile? I do note that a couple of years ago the FE increased significantly in size, but none of the local temp tables have any significant data. Would this procedure help?

VBA collects bloat which that documented process is the way to completely clear that up. Yes, make a backup copy of the database and run through that process on the backup copy... then give it a try!