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.
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.
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.