Dear visitor, welcome to Palo Community Forum.
If this is your first visit here, please read the Help. It explains in detail how this page works.
To use all features of this page, you should consider registering.
Please use the registration form, to register here or read more information about the registration process.
If you are already registered, please login here.
Palo 3.2CE - ODBC connection to SQL does not work?
Hello Jedox team and community,
I have found problem with ODBC connection to SQL server (older, does not support newer jdbc) trough System-DSN definition. The most important data source for my reports!!! The connection was in v. 3.1 CE without problem.
What has been changed and how to reach SQL server trought mentioned ODBC connection?
There is Information Dialog from ETL server:
Failed to open Relational connection xxx with URL jdbc:odbc:XXXX. Please check your connecton specification. [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
But login is verified and works for Excel and for Palo 3.1CE too. Does anybody know what is wrong, please?
Thank you.
Lubos
This post has been edited 3 times, last edit by "ldivis" (Aug 13th 2011, 10:57am)
Hello Lubos,
there seems to be a problem with ODBC-Connections which have a user and password (i.e. not simple Access or Excel files). This will be corrected in the next ETL release.
In general, to connect to a MS SQL Server you should use the specific connection type "Sqlserver". This uses JDBC and has a better performance. Or is there any reason to use ODBC instead?
Regards, Andreas
Hello Andreas,
My data source is on older SQL 7 which does not support JDBC. ODBC is in this case only one possibility as I have mentioned above. I have found in your documentations that ETL 3.2 does not support ODBC connection to Relational DBMS. (palo_etl_server_manual.pdf, 5.1 Migration from Palo ETL 3.1 to ETL 3.2) Why??? Could you help, please? I do not want to reinstall old 3.1 CE and I can not wait for new version in 12-15 months, I am affraid.
Thank you,
Regards, Lubos
This post has been edited 3 times, last edit by "ldivis" (Aug 18th 2011, 10:15am)
Only for Relational Load (=Writing) ETL 3.2 does no longer support ODBC connections. For Loading there are to many database specific issues with the JDBC-ODBC-Bridge. So you have to use the specific JDBC-based connection types for the supported database systems or connection "Access" for MS Access.
For Relational Extract (=Reading) ETL 3.2 still supports ODBC. We are still investigating about the problem with user/password assignments.
Suggestion for a solution
As mentioned in the previous posting we had a similiar problem. We solved it by using connection type Generic DB with following parameters:
- Driver: sun.jdbc.odbc.JdbcOdbcDriver (the standard java jdbc-odbc-bridge)
- URL: jdbc:odbc:<DSN-name>
- user, password of the database
Additionally we solved the connection via native DB2 connection:
- Get the file DB2jcc_license_cisuz.jar (usually located in \sqllib\java of the DB2 connection client) and copy it to the .\tomcat\webapps\etclserver\WEB-INF\lib_external directory.
- If network encryption is used, you need to provide an additional parameter in the connection, that is securitymechanism=13
Hope that helps
Kind regards
Ernst-August Stehr
