Solved

ODBC 32 and 64 bit Conflict

Posted on 2013-12-28
8
1,022 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 85

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 85
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 58
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 70

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

717 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