Solved

ODBC 32 and 64 bit Conflict

Posted on 2013-12-28
8
985 Views
Last Modified: 2013-12-28
ODBC Driver Connection problems:
1.      FYI – the PC (64 bit) has Windows 2012 Server installation and SQL Server 2012.

2.      I have created an ODBC Driver to connect to a SQL Server source on the same machine.

3.      When trying to run a report using this ODBC Driver (ignore the solution I am using as this is irrelevant for now – i.e. a Business Intelligence software as that is simply using that ODBC connect) – I encounter the following error “[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified…”

4.      Strangely, when I “Test” the ODBC Connection in an underlying connection tool it work fine

5.      Based on my research I have understood (I am not sure if I am correct) that this problem is arising due to a conflict in the 32 bit and 64 bit ODBC Drivers.

See article: http://support.microsoft.com/kb/942976 -I have indeed tried to use

6.      Strangely, when I create the ODBC driver, it doesn’t allow me to differentiate between a 32 bit and 64 bit – for example, if I use the 32 bit ODBC source manager – it still shows the driver as 32 bit/64 bit (TestSQLServerConn).  The same problem arises when I use the 64 bit ODBC Data Source Administrator.

Screenshots and description above in attached file.
ODBC-Driver-Connection-problems.docx
0
Comment
Question by:hennanra3
8 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 350 total points
ID: 39743296
Have you tried creating a different type of DSN - like a System or File DSN?

Did you create the driver with the 32 bit version of the tool - the odbcad32.exe file in Windows\SYSWOW64? If so, try removing the DSN and creating it again.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39743359
I think your application or the other tools is running under a different user...

As the DSN is defined under the User tab it will not be available for all the users I believe....

The best thing I will do, I will create under the System tab for both 32 and 64 bit in both places and give it a try.
0
 

Author Comment

by:hennanra3
ID: 39743366
Hi, nope same user and yes I tried system dsn - it doesn't work. I don't think this is the right solution - any other ideas welcome.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 84
ID: 39743367
I will create under the System tab for both 32 and 64 bit in both places and give it a try.
I suggested doing that about 45 minutes before you did. Any reason you would repeat my advice?
0
 

Author Comment

by:hennanra3
ID: 39743371
It doesn't matter - it didn't work :-) - would appreciate focus on solution rather than arguing please.
0
 
LVL 57
ID: 39743414
LSM has given you the correct advice.

You need to create a system DSN under both 32 and 64 bit ODBC managers.   The 32 bit version is:

C:\Windows\SYSWOW64\odbcad32.exe

and for 64 bit:

C:\Windows\system32\odbcad32.exe

However for reducing confusion and to aid in troubleshooting, I would use different DSN names for each (i.e.  myDSNName_32 vs myDSNName_64).

 Doing that, you should have no problem using 32 or 64 bit applications to 64 bit SQL server.

Jim.
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 150 total points
ID: 39743528
Don't get confused by the "32/64-bit" display in your ODBC DSN list. The ODBC driver is just installed as both, and that is fine.

According to the screenshots you have already noted the difference between both odbcad32 executables. We see that you use an User DSN - but that should not matter, unless the application is impersonating some other account (unlikely). Creating the System DSN, as recommended, is a good idea. Apparently that didn't help either.

What we cannot see is which DSN your application uses - make sure there is no typo. We can only see the one you used in the New Relation Connection is working; "New Relation Connection" tells me that you can't test the existing connection definitions.

Maybe you should switch on ODBC Trace in the ODBC Administrator tool (both 32 and 64 bit - use different file names!). You should at least see logging entries for trying to locate a DSN (but not sure about that, tracing might start later in the checks).

Another way is to use DSN-less connections. The necessary info is included completely in the connection string.
0
 

Author Closing Comment

by:hennanra3
ID: 39743782
Note, Qlemo also recognised as the point regarding trace is very useful.  I appreciate other comments were in good faith but they were repetitive of earlier posts or of information I had already ascertained as put in my MS Word document so didn't add specific value to LSM post.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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