February 4, 2003 at 1:38 pm
I upgraded from 7.0 to 2000 (SP3) and had some problems with ODBC connections from Access 2000. After encountering some initial problems (the exact symptoms of which I can’t recall), I also installed the new SQL Server admin programs on all the various workstations, and that fixed some things.
I prefer using the trusted connection (Windows security as opposed to SQL usernames), but the tables that were ODBC-linked in that manner couldn’t be accessed until being relinked. The error says "SQL Server does not exist or access denied."
When I relink manually, it seems OK. But we have lots of tables, so I really want to relink them all under program control (in Access using VB), but in testing that doesn’t seem to work (i.e., it makes a new connection but still doesn’t allow access).
Once manually relinked, I found that all Win2K machines worked fine, but most Win98 ones didn't (only one did)--whether I used a trusted connection or not. This, in conjunction with a new search on the MS web site, led me to the Client Network Utility. I found if I moved Named Pipes in front of TCP/IP on a Win98 machine, it started working! (Even though SQL Server is set to listen on both).
When this occurred, it got a different error message--“Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.” (This happened even on connections NOT set up to be trusted, and specifying a username/password didn’t work either.) So this problem seems to be separate; although since it may be sort of mixed in with the other one, I’m describing it here.
So I have these questions:
1. Has anyone else had this much trouble with ODBC connections after upgrading?
2. Can somebody make any sense out of these symptoms?
3. Why would SQL Server not respond on TCP/IP as in the above scenario?
4. I’ve tried various combinations of parameters in the connect string, and still have trouble, even though I don’t think I’m leaving out anything important. So when programmatically setting the connect string, are there any particular parameters you’ve found that can cause problems if omitted?
February 4, 2003 at 1:42 pm
Upgrade to SQL Server 2000 also upgrades MDAC to 2.6. This may related to your problem. Try to upgrade MDAC component to 2.6 too in your workstation machine which you had problem.
After apply SP3, The MDAC is in 2.7. So you may go to 2.7 directly.
Edited by - Allen_Cui on 02/04/2003 1:43:40 PM
February 4, 2003 at 3:24 pm
quote:
Upgrade to SQL Server 2000 also upgrades MDAC to 2.6. This may related to your problem. Try to upgrade MDAC component to 2.6 too in your workstation machine which you had problem.
Actually, fairly early in the troubleshooting process, I did just that (put MDAC 2.6 on a workstation), and it didn't help anything.
Later, as I related in the original article, I put the SQL Server admin tools on all the workstations, which means they were also updated to MDAC 2.6 in the process. That DID help one problem, but not the current problem (the TCP/IP vs. Named Pipes question).
quote:
After apply SP3, The MDAC is in 2.7. So you may go to 2.7 directly.
Somewhere in the troubleshooting process after seeing problems, I also put SQL Server SP3 on one W98 workstation, but it didn't help.
And this doesn't explain why Win2K works but Win98 doesn't.
MDAC is so confusing. The "MDAC Home Page" on the MS site has all these different releases, patch kits, etc., but doesn't ever say under what circumstances you need what version.
For instance, it doesn't say if you need the same version on client & server ends. It doesn't say if the server end needs to be the same or later version than the client (or the reverse). It doesn't say when it's safe to upgrade client or server. It doesn't say "SQL Server version x (or Access version x, etc.) requires MDAC version y." I'd also appreciate it if somebody could shed more light on MDAC for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply