Solved

ODBC 32 and 64 bit Conflict

Posted on 2013-12-28
8
1,007 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Hidden queries with "~sq_f" and "~sq_r" 3 20
Merge join vs exist 3 28
Adjust the codes 3 37
Displaying images within Access Form 6 26
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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