| Groups | Blog | Home |
sql server odbc : ODBC call failed - weird behavior
I have been working on updating an Access 2000 application that uses linked ODBC tables to connect to an SQL Server 2000. My development machine is connected to the server over a Microsoft LAN, at 100Mbits. Both the Office 2000 on my development machine and the SQL server has all the latest SP's Everything worked fine for months until today. I am not aware of any changes to ether machine since yesterday. Now, there is one table that can not be opened any more. I can access all the other tables ecxept that one. When I try to open it I get the message "ODBC call failed". The table is displayed but with #Name? in all columns for all records. Once that has happened I get the same message for all tables. In addition to "ODBC call failed" there is also "[Microsoft][ODBC SQL Server Driver][Communication link failure (#0)". When I close down and reopen the app I can again access all tables except the bad one. At first, I thought the table had gotten corrupted, but I can open the table just fine on the server. If I set up an ODBC on the server with the same name I and run my application there. the problem table opens fine. I removed the ODBC and set it up again with the same properties, client uses TCP/IP network library, and I am using SQL Server authentication. I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem. I ran I am wondering if I should update to 2.8 If anyone has a suggestion I would appreciate it very much. Ragnar
It could be an MDAC issue. You could run the MDAC component
checker on a machine where the app works and another machine where the app gets the ODBC error and see if there are any differences in MDAC versions. You can get component checker from: http://msdn.microsoft.com/data/downloads/tools/default.aspx You could also turn on ODBC tracing then reproduce your error and then turn tracing off again. You could open up the ODBC trace log, look for specifics of what's failing and try to track down the issue from there. -Sue On Wed, 10 Mar 2004 19:05:29 -0500, "Ragnar Midtskogen" [quoted text, click to view] <ragnar_ng@optonline.net> wrote:
>Hello, > >I have been working on updating an Access 2000 application that uses linked >ODBC tables to connect to an SQL Server 2000. My development machine is >connected to the server over a Microsoft LAN, at 100Mbits. >Both the Office 2000 on my development machine and the SQL server has all >the latest SP's >Everything worked fine for months until today. I am not aware of any changes >to ether machine since yesterday. >Now, there is one table that can not be opened any more. >I can access all the other tables ecxept that one. When I try to open it I >get the message "ODBC call failed". The table is displayed but with #Name? >in all columns for all records. >Once that has happened I get the same message for all tables. In addition to >"ODBC call failed" there is also >"[Microsoft][ODBC SQL Server Driver][Communication link failure (#0)". >When I close down and reopen the app I can again access all tables except >the bad one. >At first, I thought the table had gotten corrupted, but I can open the table >just fine on the server. If I set up an ODBC on the server with the same >name I and run my application there. the problem table opens fine. >I removed the ODBC and set it up again with the same properties, client uses >TCP/IP network library, and I am using SQL Server authentication. >I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem. >I ran >I am wondering if I should update to 2.8 > >If anyone has a suggestion I would appreciate it very much. > >Ragnar >
Thank you Sue,
I had run ComponentChecker on my development machine and it now has 2.6 SP2 Refresh wih a couple of files being slightly newer than expected, ODBC32.dll and ODBCCP32.dll. The Server had 2.7 SP1 Refresh with about 8 files slightly newer than expected, including the ODBC32.dll and ODBCCP32.dll. I thought I had run a trace, but I did not realize I had to switch it on. I ran a trace and found these error lines toward the end. This trace was only running while I opened the problem table in the DB window. MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR) HSTMT 09022400 DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionRead (recv()). (10054) DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. (11) I am not familiar with ODBC calls, but I see there were two error messages I have done some more testing and found that I can open a form that displays the contents of this table in a listbox with no errors. The RowSource for the listbox is a select query that selects all fields of the table. Yet, if I create a stand alone query that does the same, it fails. I am beginning to suspect network problems, possibly the NIC on the server. We had problems with that before and it was replaced. The connection into the main network from my machine also goes through a switch, which could be acting up. I still think I am going to update MDAC to the same as the server, or do you think 2.8 would be better? Ragnar
You'd definitely want to be on 2.7 SP1 at least due to all
of the bug fixes for it since 2.6 SP2. I'd try to go that route first being that it works on your server. 2.8 has a lot of security changes and it should be tested in another environment if possible before installing. It's the base mdac version for Windows 2003 but I don't think anything other products, service packs, etc are installing it at this point. The list box testing you did is odd, otherwise I'd suspect network issues as well (or wouldn't rule them out anyway). Were both of the connections, data source properties for the list box and the stand alone query exactly the same? -Sue On Thu, 11 Mar 2004 13:13:12 -0500, "Ragnar Midtskogen" [quoted text, click to view] <ragnar_ng@optonline.net> wrote:
>Thank you Sue, > >I had run ComponentChecker on my development machine and it now has 2.6 SP2 >Refresh wih a couple of files being slightly newer than expected, ODBC32.dll >and ODBCCP32.dll. >The Server had 2.7 SP1 Refresh with about 8 files slightly newer than >expected, including the ODBC32.dll and ODBCCP32.dll. > >I thought I had run a trace, but I did not realize I had to switch it on. I >ran a trace and found these error lines toward the end. This trace was only >running while I opened the problem table in the DB window. > >MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR) > HSTMT 09022400 > > DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP >Sockets]ConnectionRead (recv()). (10054) > DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General >network error. Check your network documentation. (11) > >I am not familiar with ODBC calls, but I see there were two error messages > >I have done some more testing and found that I can open a form that displays >the contents of this table in a listbox with no errors. The RowSource for >the listbox is a select query that selects all fields of the table. >Yet, if I create a stand alone query that does the same, it fails. > >I am beginning to suspect network problems, possibly the NIC on the server. >We had problems with that before and it was replaced. The connection into >the main network from my machine also goes through a switch, which could be >acting up. > >I still think I am going to update MDAC to the same as the server, or do you >think 2.8 would be better? > >Ragnar > >
Thank you Sue,
[quoted text, click to view] > You'd definitely want to be on 2.7 SP1 at least due to all > of the bug fixes for it since 2.6 SP2. I'd try to go that > route first being that it works on your server. That is what I did. Unfortunately the problem is still there. [quoted text, click to view] > The list box testing you did is odd, otherwise I'd suspect > network issues as well (or wouldn't rule them out anyway). > Were both of the connections, data source properties for the > list box and the stand alone query exactly the same? They both use the same linked table. I suspect the network connection now, I tried running my app on another machine on the network and the problem disappeared. Ragnar
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |