ODBC 32 and 64 bit Conflict
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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?
ASKER
It doesn't matter - it didn't work :-) - would appreciate focus on solution rather than arguing please.
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\odbcad
and for 64 bit:
C:\Windows\system32\odbcad
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.
You need to create a system DSN under both 32 and 64 bit ODBC managers. The 32 bit version is:
C:\Windows\SYSWOW64\odbcad
and for 64 bit:
C:\Windows\system32\odbcad
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.