Error in SQL Query

[appleglaze28]

This is a first time error. My source changed so I had to change my query for loading data to my TM1 Cube and I got this error.

Code: Select all

1824   [2]   ERROR   2012-09-12 07:04:55.727   TM1.Process   Process "LOAD_Commission_MKT_1": : Execution was aborted. Error file: <TM1ProcessError_20120912070455_LOAD_Commission_MKT_1.log>

When I open the log this is what the log says:

Code: Select all

Error: MetaData procedure line (0): Error executing SQL query: 

Hope someone can help me troubleshoot this.

[asutcliffe]

So what has actually changed in your source? Have you verified that the SQL query in your TI process is valid?

[appleglaze28]

tables are the same just the source database I've been given access to. I usually do my SQL Query in SQL Management Server and then copy it to TM1 its easier that way. I'm able to preview the Query but when it comes to uploading the data to the TM1 Cube I get that error. Its something new for me to encounter so I'm not sure what is the possible problem.

[asutcliffe]

I'm able to preview the Query but when it comes to uploading the data to the TM1 Cube I get that error.

By this do you mean that when you open the TI process in the editor, the preview box on the data tab contains data? If so are passing it any parameters that might render the sql invalid? Are you modifying any of the TurboIntegrator Local Variables (eg DatasourceNameForServer, DatasourceQuery) in the prolog?

[appleglaze28]

I'm not passing any parameter nor local variables. Yes the preview box contains data and I was able to set additional variable in the variables tab and write the query in the Advance Tab.

I even tried to use the same query to create a new dimension and use 1 variable in the query as my element and I still get the same error.

I tried to run my old query it still works. I was just provided direct link to an application's database to ensure data that I load in TM1 are always update compared to the old one wherein certain tables I use are not update.

[Duncan P]

Have you checked that the ODBC connection has access to all the objects referenced in the statement? Can you use the query from e.g. Excel using the same ODBC connection?

[tomok]

The first step to troubleshooting an SQL problem with TI is to ASCIIOUTPUT to a text file the SQL string you are passing. Copy and paste it into a command window for whatever database tool you are using to make sure it will actually run. If that passes muster make sure the ODBC connection is set up properly and can connect to the database. If you are good there then make sure the account the TM1 service is running under has all the appropriate security access to run the query. If that is OK, make sure you don't have syntax errors in any DatasourceNameForServer or DatasourceQuery or other TI local variables. 99% of the problems will fall in one of these. If these aren't the cause then good luck.