Solved

ODBC 32 and 64 bit Conflict

Posted on 2013-12-28
8
973 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
 
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
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.

 

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 68

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how the fundamental information of how to create a table.

863 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

26 Experts available now in Live!

Get 1:1 Help Now