Solved

Connecting to new SQL Server

Posted on 2014-04-21
13
496 Views
Last Modified: 2014-05-02
We replaced a server 2003 SQL 2005 32 bit with a server 2008 R2 SQL 2005 64 bit.  Programs (using ODBC) updated on the new server run fine from the workstations (mostly Win 7 64 bit).  The same ODBC definition is unable to see or connect to the new server from the workstations.  An MS Access ADP is unable to run from the workstations;  it runs fine on the new server.  I have checked the SQL client drivers, comm protocols, and permissions.
Please help.
0
Comment
Question by:BFOG
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<The same ODBC definition is unable to see or connect to the new server from the workstations.  >>

 You need to be carefull...there are two versions of the ODBC32ad.EXE program, a 32 bit and 64 bit one.  

Which you use depends on the Office version you have.  Sounds like your using 32 bit, so the one you need to use is:

C:\Windows\sysWOW64\ODBD32ad.EXE

The one from the control panel gives you the 64 bit one, which is in C:\windows\system32

Confusing right?

Make sure anything you define is for a system DSN.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't use the ADP format (because it never gave me anything I couldn't do more easily in an .MDB) and MS stopped upgrading it many years ago (around A2007).  Is it possible that the ADP doesn't support SQL Server 2008 R2?
0
 

Author Comment

by:BFOG
Comment Utility
It is SQL Server 2005 64 bit running on Server 2008 R2.  The ADP provides significant performance improvement.
0
 

Author Comment

by:BFOG
Comment Utility
The office version is 2003.  I will check my client and see.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Versions of Access through A2010 (and possibly A2013 but I don't remember) support the .ADP but the .ADP cannot support newer versions of SQL Server.  I know it can't do 2012 but I don't remember where it stopped.  2005 may be the end of its road since 2008 R2 came out AFTER A2007.

That is just the way software works.  It can be made to support versions created prior to itself because those are known entities.  It cannot be made to support versions created subsequent to itself because those are not known at the time UNLESS MS makes an update to make it possible.  And they may have elected to not update the ADP to go to SQL Server 2008 R2 because by the time that version had been released, the ADP was already deprecated.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
ADP support was dropped in Access 2013.  

 A2000/2003 should connect to SQL 2008 as long as you don't do any design changes in tables.  If your going to do that, then I believe 2007 SP1 is the earliest you can be on.

 I haven't used ADP's in years, so I'm a little out of date on things.  Maybe someone else with some hands on with ADP's will jump in and have a definitive answer.

 If not, I can try it.

Jim.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

ADP is designed to use ADO and has an own connection management which has nothing to do with ODBC, it is completely independent of the ODBC settings of the system so you do not need to check anything there.
When you look into the server connection of the ADP all settings must fit for your new server. All ADPs (independent of which Office or SQL Server version) can connect to a SQL Server because it's a simple ADO connection only restricted to use the SQLOLEDB driver.

Normally ADPs are constructed to also DESIGN objects of the used SQL Server, this is what you can't do with an older Access with a newer SQL Server (but the Access designers are not worth to be used, you should only use the SQL Server Management Studio or other professional designers to design the SQL Server objects). You can't also work with newer datatypes or some of them only limited. Another thing is that ADPs are using special stored procedures to work with SQL Server and so you can get in trouble if there are newer versions of them which maybe don't work in the expected way. So the general rule is: Don't use ADPs of older Office versions with SQL Server versions which didn't exist at the time the Office version came out. As you can deliver Access runtime versions until A2010 working with ADPs with up to SQL Server 2008 and for free there is no really sense in using A2003 for that.

But back to the connection problem: You would get a connection to the server in any way with any ADP version. You can also simply test if it is a pure connection problem by simply opening an Excel sheet, inserting a standard module and create a simple ADO connection sub with SQLOLEDB, that's the same what ADP does in the background. You can also use the SQL Server Profiler to see what's happening in the background, sometimes there are more detailed information using this way.

Please remember that any new SQL Server installation is protected to be used in the network, you need to enable TCP/IP protocol and mostly also the Named Pipes protocol. In Windows 7 sometimes I had the issue also on client machines where you can also configure the client protocol with SQL Server Configuration Manager.

You should of course also check your local firewall setting if the needed port is free, disable it temporarily to see if that's the problem.

Next is the use of the FQDN (full qualified domain name) of your server. In big networks sometimes subdomains cannot be found through the local DNS if only the servername itself is used in the connection. Be sure to always use the FQDN to avoid such problems. You can of course also use the IP address directly as they normally change only rarely in case of servers. Don't forget to use the instance name in your connection string also if you have more than one instance running on the server.

Cheers,

Christian
0
 

Author Comment

by:BFOG
Comment Utility
Thanks All.  I am still trying your suggestions.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

it would be helpful if you show the exact error message and also what's the result in trying to open a connection using VBA and ADO in Excel.

Cheers,

Christian
0
 

Author Comment

by:BFOG
Comment Utility
This is actually two separate problems.

One is the inability to connect via odbc to one database while having the ability to connect to a different database on the same server via odbc.  The error message I receive is attached.  This is for the connection unable to be made.  The program using ODBC works fine when run on the server itself.  When I try to launch it from a workstation (that successfully connects to a different database on the same server), the connection cannot be completed.  I am so befuddled at this point I am not sure what to try next.  If I try install a native client on the workstation,  it fails and tells me it was due to a higher version of the client already on the workstation (which I believe to be false).
The ADP problem will be expressend in another question.
SQL-Err.doc
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

did you try everything which is explained on the following page?:
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

Again: Did you try to create a connection with ADO in Excel VBA like I said above and what was the result?

ADPs don't use ODBC and either no SQL Native Client, did you try to create a connection using the FQDN of the server including the instance name like I said above and what was the result?

It is not helpful if you repeat your problem without answering the questions.

Cheers,

Christian
0
 

Author Comment

by:BFOG
Comment Utility
Not yet.
Should this work for SQL AServer 2005?  (It is not a SQL 2008 environment - just a MS Server 2008 R2 server)

I don't have much time to work on this.  Please be patient.
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 300 total points
Comment Utility
Hi,

I have time, I'm not unpatient, it's your problem and not being able to connect to the server is in my eyes a problem with high priority after moving from one to another server, but it's on you how much time you want to invest and when. I simply reply when you do..:-)

The are no big differences between 2008 and 2008 R2 in case of connection (differences see here: http://msdn.microsoft.com/en-us/library/bb500435%28SQL.105%29.aspx) so if you have checked anything from the link you should in most cases already be able to connect. It's similar in 2005 but there is no "allow remote connection" property there. But you said your problem is with the new server and not with 2005.

Cheers,

Christian
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now