ODBC connection fails intermittently

ODBC connection fails intermittently

Post by Bo Berglun » Thu, 17 Apr 2003 06:46:49



I have a problem in a VB6 application that has been patched for many
years and is now used towards a SQL-Server database.
It uses a local Access97 (DAO3.51) database for its "daily operations"
but the real data are on SQLServer. In order to process normally we
have made table connects using ODBC so that we can run DAO queries
with joins between the local and remote tables. This usually has been
working OK, but now we see occational ODBC failures for some
operations. When the app starts up it connects the SQL tables into the
Access97 database using an ODBC string that defines the datasource
name, user login, server name, network library to use, switches off
windows authentication etc.
From then on many operations are done using these tables. But then
later in one and the same procedure where we make several calls using
joins with both local and foreign tables then - poohh! - we get a ODBC
connection error. Always at the same finishing query...
Now if we go into the Control panel and open the data source and walk
through it and set the server name, authentication (SQL) and network
configuration (TCPIP), then go back and run the same operation in the
program (which has not been closed down) - it works OK!

Question:
Why is the ODBC connection changing its properties so that the
connection fails suddenly when it did not from the start? It looks
like it is not using the connect string we defined when we connected
the tables.
We override the servername, authentication and network library in our
ODBC string just to make sure it will work, and it does at the
beginning, but does not last all through....
But if the system data source we use is manually changed to reflect
the settings we use then it works. Problem is that we cannot do this
because the user is not foreseen to be an administrator.

Any tips at all?

/Bo

 
 
 

ODBC connection fails intermittently

Post by Kun Cheng [M » Sun, 20 Apr 2003 00:13:53


If you feel comfortable, run odbc trace to find out why connection string
is changed. If you have a good repro, consider opening a case with PSS
Access support on the issue.

HTH

This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Kun Cheng
Microsoft Support Engineer
--------------------

| Subject: ODBC connection fails intermittently
| Date: Tue, 15 Apr 2003 23:46:49 +0200


| X-Newsreader: Forte Free Agent 1.92/32.572
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: h5n2fls33o827.telia.com 213.66.182.5
| Lines: 1        
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:350906
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have a problem in a VB6 application that has been patched for many
| years and is now used towards a SQL-Server database.
| It uses a local Access97 (DAO3.51) database for its "daily operations"
| but the real data are on SQLServer. In order to process normally we
| have made table connects using ODBC so that we can run DAO queries
| with joins between the local and remote tables. This usually has been
| working OK, but now we see occational ODBC failures for some
| operations. When the app starts up it connects the SQL tables into the
| Access97 database using an ODBC string that defines the datasource
| name, user login, server name, network library to use, switches off
| windows authentication etc.
| From then on many operations are done using these tables. But then
| later in one and the same procedure where we make several calls using
| joins with both local and foreign tables then - poohh! - we get a ODBC
| connection error. Always at the same finishing query...
| Now if we go into the Control panel and open the data source and walk
| through it and set the server name, authentication (SQL) and network
| configuration (TCPIP), then go back and run the same operation in the
| program (which has not been closed down) - it works OK!
|
| Question:
| Why is the ODBC connection changing its properties so that the
| connection fails suddenly when it did not from the start? It looks
| like it is not using the connect string we defined when we connected
| the tables.
| We override the servername, authentication and network library in our
| ODBC string just to make sure it will work, and it does at the
| beginning, but does not last all through....
| But if the system data source we use is manually changed to reflect
| the settings we use then it works. Problem is that we cannot do this
| because the user is not foreseen to be an administrator.
|
| Any tips at all?
|
| /Bo
|

 
 
 

1. OpenDatabase fails intermittently

Hi All,

I've got an application that stores hundreds of small .MDB files in an
"object archive". During the course of processing, I copy these files to a
temporary directory and work on them.

Normally all goes well, but occasionally I get a message that says:

"Run Time Error 3049.
Can't open database XXXXX.  It may not be a database that your application
recognizes, or the file may be corrupt."

If I open the XXXX file with Access, the file opens without incident, and
everything seems fine.

What gives?

--
My reply E-mail is munged.
Remove the "NoLunchMeat"  to reply by E-mail.

2. opening forms that may encounter errors

3. MS Oracle driver intermittently fails with error 7399 and OLE error 0x80004005

4. informix online 5.0 crash

5. Scheduled Backup Job fails intermittently

6. Spelling check

7. DB-Library: dbopen fails intermittently

8. checkpoint affecting sqlldr direct path

9. Dts Package Fails Intermittently

10. ct_init() fails intermittently

11. BCP fails intermittently

12. Intermittently Failing Trigger

13. JDBC Connection Hands Intermittently on a Create a table or Index