ODBC connection interfering with Production

[mdjks]

I'm using an ODBC (read only) connection in an Access 2007 db (2003 format) to a SQL server 2000. I left a 2 table query open for about an hour while I went to a meeting. While gone apparently the application that runs on this server went down and the DBA had to kill my query because it was blocking all other queries in the application.

Could someone point me to information on how an ODBC connection can affect the production database? It has always been my understanding that using a read only ODBC connection could not harm production in any way.

 

[Banana]

Exactly how are you making it a read-only? There's couple different ways to make it read-only.

I am not familiar with SQL Server, but I don't think 2000 had MVCC, meaning that if you requested for a lock, even if it's a read-only, it can potentially create deadlock because a lock is necessary in order to read the rows.

This can be worked around by using ADO to retrieve the data and closing the connection. This gives you a disconnected recordset so the lock time is minimized. Alternatively, you can just import the table, which accomplish basically same thing (being mindful to delete it afterward, of course)

Also, you should make sure that the query returns a Snapshot type recordset (especially if you are not using passthrough query to process the query), not Dynaset recordset; this will tell Access that this is truly a read-only query.

The thing is that it's hard to say whether it's Access, ODBC, or SQL Server responsible for this problem because all of this depends on different factors (e.g. lock passed, types of recordsets, and manner of connecting). You will need to talk with your DBA and read up on documentations how to avoid this kind of behavior.

If you search KB articles, you should be able to find a good FAQs on "Optimizing Server/Client Performance", "Jet/ODBC Connectivity" and a couple others; you'll have a good base to build on and ensure that your queries are carefully written.

HTH.

 

[mdjks]

Thank you for your reply, you have given me enough information that I should be able to research the problem. Very helpful.