Basic SQL Server help

[gbfell]

Hello people,

We have designed an Access 2000 front end/SQL Server 7 backend. We have a server than runs only SQL server, with 2 1000 mhz chips, and 512 mb of ram. We have ~10 clients talking to this server, and the db is only about 70 mb. Each client has its own MDB file, with linked tables thru ODBC to the SQL Server. We have been plauged by 'ODBC call failed' messages, usually they happen to each machine at the same time.

I noticed in the Enterprise Manager Current Activity window, there are numerous processes, BUT they all share the same host, which is a workstation owned by the guy that designed the Access linked tables, forms, etc. Does this mean all the data is passed through this PC? I relinked all of the MDB files (each machine runs their own) locally, and the host name changed to the local machines pc name. They seem to run much better (and faster). What exactly does this host do? And what causes the status of some processes to be sleeping? Thanks in advance.

 

[tlbroadbent]

1) Access carries the host name of the PC that created the links. I consider this to be a bug in Access. You can change it as you did by recreating the links on each PC. I beleive you can also make Access choose the name of the host by blanking out the host name in the options when linking the SQL tables.

2) The fact that the connections show an incorrect host doesn't mean the PCs were all routing through that other PC. Access is simply sending incorrect info (stored host name rather than real host name) as part of the connection string.

3) Process statud "Sleeping" just means the connection isn't actively doing anything at the time of the snapshot.

4) "ODBC call failed" messages can have many causes. Do you have any additional info about the error messages? Are the errors occurring on certain tables? Are the users failing to connect? Are they updating or only viewing the data? There are many questions that may need answers in order to determine the reasons for the errors. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.