Connecting TI to Teradata

[bshaver]

I'm having trouble running a simple Teradata query in TI. This query works in WinSQL on the TM1 Server.

SELECT *
FROM SDWP.ACCT_DAY A
WHERE A.ACCT_MONTH = '200901'

However, it returns an "unable to open connection to database" error in TI.

In my processes that download from other ODBC sources, the table name is preceeded with the datasource name, i.e. "FROM PSFSPRD.LEDGER A", where PSFSPRD is the datasource name and LEDGER is the table name. While this is not required in WinSQL, it is in TI.

Using that same logic here, "FROM SHAWPROD.SDWP.ACCT_DAY", where SHAWPROD is the datasource, SDWP is the database and ACCT_DAY is the table, does not work.

I appreciate any and all suggestions. Thanks.

[belair22]

"Unable to open connection to database". Suggests your ODBC connection isn't right (query isn't the issue here).

Are you testing your TI using TM1 directly on the server (ie using terminal services or citrix)?

[jim wood]

Have you setup the ODBC connection as a user connection or system connection?

[bshaver]

The connection is a system connection.

I am connecting to the TM1 Server using Terminal Services Client. WinSQL is loaded directly on the server and the query works fine. I don't think it's a problem with the connection.

Thank you.

[belair22]

Can only be 2 things:
1) ODBC Connection setup not correct
2) Incorrect Username/Password supplied to the ODBC connection from TM1
Under both these circumstances TI will return a "unable to open connection to database" error message

If a SQL query in the Datasource tab of a TI has incorrect syntax or attempts to reference non-existant objects TM1 returns a "SQL Statement Failed" error mesasge.

[Steve Rowe]

I may be misremembering but doesn't the user that the TM1 server/service runs under need to have the rights to open ODBC connections? This is normally a default but if your IT department is particularly paranoid then you'll need to check this too.

Cheers

edit so the post makes sense!

[jim wood]

Does WinSQL prompt you for a password when you connect? I connect to Teradata using Queryman and this requires a password everytime. It doesn't store the password in the Teradata ODBC settings. I've not tried connecting TM1 to Teradata myself (yet) so I can't supply you with a work around. This may be one for IBM/Cognos/Applix support,

Jim.

[bshaver]

When I launch WinSQL, it asks which datasource I want to use and for a userid and password. This is the same id and password I enter into TI.

[belair22]

I may be misremembering but doesn't the user that the TM1 server/service runs under need to have the rights to open ODBC connections? This is normally a default but if your IT department is particularly paranoid then you'll need to check this too.

Cheers

edit so the post makes sense!

Yes - the service should be run under credentials that have access to the ODBC source (can potentially be problematic if the services is run under a local admin).