Okay-- if anyone can solve this they are truly the SQL genius! We are getting this error when we run a VB program that we use to access an SQL database on a server across our network on a workstation. In fact we get this same error when we even run the program on the server where the SQL database is running or on any of our workstations. Here is the error message:
08501:[Micorsoft][odbc sql server driver] communications link failure
Now the odd thing is that many other functions in the workstation application work fine and retrieve data from SQL but certain data requests by the workstation application fail with the above error message and we get this message consistently. Even though it appears that different workstations running the identical Vb application will get this error consistently but in different locations when running the application. We were running SQL 6.5 on an old server, with the workstation application for literally years without any problems. We also decided to upgrade to a new server, installed server 2000 operating system and the latest version of SQl -- moved all the databases pointed the workstations odbc at this new server and get exactly the same error in the same location in the workstation applications. The programmer that wrote the application and designed the database in SQL can't find the problem and a number of other computer "experts" also could not find the error. We did add a new linksys DSL router/firewall but everything kept working after this installation for several weeks so I don't know if this is the problem on the network. THe programmer also noted that he had problems using terminal services on our network to connect to his office computer and decided that there must be some network issues that are causing the ODBC communicaitions to fail and also terminal services to fail-- or of course they may be unrelated. Has anyone ever seen this ODBC communication error in their travels through SQL implementations? Any help will be greatly appreciated. If we can't fix this we will have to abandon a software application that has been used for over five years and just too complex to rewrite.
Jeff KilpatrickWell, this challenge is not really for a "true DBA", simply because it's related to ODBC driver for SQL Server. Link failure may be caused by several things, including improper closing of resultsets without notifying the server that no more records are needed. The definition ofthe error is:
The communication link between the driver and the data source to which the driver was connected failed before the function completed processing.
And that's what you need to investigate. The reason why it worked with 6.5 may be as simple as the version of the driver. So all the seeming mysteries are probably lying right in there.|||Not playing around with Windows XP SP2, are you?
Anyway, It sounds as though there is an ODBC DSN set up on each of these machines. Go through the configuration of this DSN on one of the affected machines, and see if the test connection button comes back successfully. If it is successful, then you may be looking at a client (i.e. VB Application) problem.
If the test is successful, have the programmer assemble all of his connection strings. If any of them specify PROVIDER as one of the attributes, then you may have your suspect. An ODBC DSN specifies PROVIDER and DATA SOURCE for most connection strings it is used in. Most often, it also specifies DATABASE. I have not experimented with it so much that I know if a connection string can override the DSN, or not, but it is worth a check. For this line of questioning, it would help if you could identify what functions seem to be erroring out. If it is random on each machine, and each attempt, then this will be a pain to figure out.
Another place to check is to make sure you have at least MDAC 2.7 on all of the machines. If you have applied SQL SP3 to the SQL box, then that box at least should have MDAC 2.7 sp1, I think. Microsoft has an MDAC version checker available on their website.
Hope this helps.|||We recently moved from an older (SQL 7 I think) server to SQL2000. Our VB application was using a DNS pointer to point to the SQL server.
This no longer worked once we moved the data to the SQL2000 server. As the poster above mentioned, I ran across a suggestion to run the MDAC patch. There is actually a newer version out now MDAC 2.8. I first ran this on my desktop and then we had to push it out and install it on all the clients which needed to connect to the new server.
Once we did that, we were able to connect to the SQL 2000 server without any problems using the DNS pointer.
A note, with ODBC, the pointer will not show up on your server list, you have to just type it in manually.
Mohamed Hussein Kotat wrote:
Mohamed Hussein Kotat wrote: