Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ODBC 32 and 64 bit Conflict

Posted on 2013-12-28
8
Medium Priority
?
1,082 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 1400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 71

Assisted Solution

by:Qlemo
Qlemo earned 600 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 October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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