ODBC - Clustered Servers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2002 -

I'm converting my application from using tables in an Access db to SQL server. Application works fine in test environment, but behaves differently and encounters errors running some queries in model office environment. Difference between environments is - test server is not clustered, model office server is.

Any help would be appreciated. Thanks.
 
Hum, I don't know much about the clustering issue, but I never seen a post
here making any kind of reference to this being some type of problem.

can you freely make and build quires using the enterprise manager? My guess
here is some type of permissions problem. I would as mentioned try Excel, or
the EM tools to see if they work ok on the databases.
 
Use of Access with clustered SQL Server is rare, but yes, there have
been reports of problems. In fact, I don't think there has ever been
a report of success, but then, you wouldn't expect anyone to report
that anyway would you :~). Generally, the reports have been of ODBC
errors and connection failures. I don't think anyone would be surprised
to see Access loose all ODBC connections when there is a handover
within the server cluster, but there have also been just the nebulous
reports like yours: errors & failures, and (perhaps incidentally) use
of a clustered server.

(david)

cmw said:
Access 2002 -

I'm converting my application from using tables in an Access db to SQL
Click to expand...
server. Application works fine in test environment, but behaves differently
and encounters errors running some queries in model office environment.
Difference between environments is - test server is not clustered, model
office server is.
 
Use of Access with clustered SQL Server is rare, but yes, there have
been reports of problems. Generally, the reports have been of ODBC
errors and connection failures, not 'freezing'. 'Freezing' is itself
a rare symptom, so I'm not surprised that I've never seen it reported
in conjunction with a SQL Cluster.
 
Thanks, David.

Are you aware of any source of information which might be helpful or is this a hopeless case?

In this instance, the particular problem I'm encountering is a query which uses a join between two tables with a one to many relationship between table A and B. The dba put a trace on the transaction and found that what was being execuited was multiple select statements - one select to table A for each record on table B. Same query executed on non clustered server with approximately the same amount of data takes milliseconds, same query executed against the same server through QueryAnalyzer also takes milliseconds.
 
hmm - that doesn't look like a 'cluster' problem, just normal Jet stupidity.
That is, it looks like the query optimiser has just made a bad choice, and I
don't know how the choice would be influenced by clustering. (I wonder if
the clustered server returns different database statistics information?)

AFAIK, pretty much the only method to avoid 'Jet stupidity' is to use
pass-through queries or stored procedures.

Sorry I can't offer more help
(david)




cmw said:
Thanks, David.

Are you aware of any source of information which might be helpful or is this a hopeless case?

In this instance, the particular problem I'm encountering is a query which
Click to expand...
uses a join between two tables with a one to many relationship between table
A and B. The dba put a trace on the transaction and found that what was
being execuited was multiple select statements - one select to table A for
each record on table B. Same query executed on non clustered server with
approximately the same amount of data takes milliseconds, same query
executed against the same server through QueryAnalyzer also takes
milliseconds.
 
Back
Top