I have a front-end in Access and migrated the backend to our Teradata server. I have created my ODBC and when I run any query that is stored in the front-end application itself (not a pass through query) the front-end application will run, just a tad slow. Not errors. Takes about 2 minutes to run and return information. When my colleagues run theirs, they are averaging 15, 30 or 45 minutes to get return results. Not sure why the variance. I was reading some Teradata performance blogs and saw many created pass through queries and I deicded to try that. However, the query that works fine with just ODBC and displays my results was changed to passthrough, it now says: ODBC call failed. Object year_table does not exist. The table IS in my Teradata and it IS ODBC, and I have coded it to where it is called in the SQL code. Any advice? Is there a way to just store my queries on the server side in the Teradata and ODBC to them and not do the pass through?
I am not sure what you mean code or set. I created the tables in Teradata using SQL Express and then uploaded the data into the tables using the import method. So the data is in the database.
How are the users profiles configured. Variance of the results of the same query executed by different users depends primarily on 2 factors. one is the load on the system and the other is the profile through which the user runs the query(Also sometimes the TASM settings if present play a role in the time lapsed). Can you please let know what you did for converting a query to pass through? Can you check if your query is referncing a wrong table or the alias in the query is not properly used. Are any simple queries getting executed or is it saying ODBC CALL failed. If yes then it could be a connectivity issue. Repeated identical queries can be created as macros and get executed.
If you are creating a table in SQL Assistant and do not include the databasename qualifier, the table will be created in the default database (your logon). You can also set the databasename using a 'database databasename' command that will consequently use that databasename as the qualifier to any subsequent table references. I was just thinking that maybe somewhere your process could not find the table because it was possibly looking in the wrong database.
As goldminer said, different users might have different default databases (if there's no default set on user level it's the user himself). so you need to qualify the tablename in your select using mydatabase.year_table or change the session's default database after logon using database mydatabase;