TM1 Forum

Posted: **Tue Mar 10, 2009 4:28 pm**

Has anybody ever done this?

My current formula is this, I have put it in the Data tab in my TI process.
ODBCOutput('TM1 Output',expand('insert into expensesexport ([versions],[subsidiaries],[expenses],[expensemeasures],[MTH],[VAL]) values ('%v1%','%v2%','%v3%','%v4%','%v5%',%v6%)'));

I have the ODBCOpen formula in my prolog tab and the ODBCClose in my epilog. I have checked to make sure that the log in that I created works as well. The error that I get say that the sql statement is incorrect, but the statement does recognize the values in the TM1 cube. Does anybody have any ideas as to what might be wrong with my query?

Posted: **Tue Mar 10, 2009 4:38 pm**

Not sure, your best bet is to ASCIIOutput your expand statement and see if that makes sense to a proper SQL client.
Cheers

Posted: **Tue Mar 10, 2009 4:44 pm**

Already did that piece. still have some issues.

Posted: **Tue Mar 10, 2009 5:08 pm**

Is anything supposed to occur after the ODBCOpen query is run?

Posted: **Tue Mar 10, 2009 8:22 pm**

I think you've relied on the syntax in the help file - which is not always a good thing. If you are inserting string values, you need to quote them - whoever wrote the helpfile didn't know that. So I second Steve's suggestion - the sql statement written to a flat file should look like

"
insert into expensesexport (
versions,
subsidiaries,
expenses,
expensemeasures,
MTH,
VAL
)
values (
'Actual',
'Sub1',
'Salaries',
'Current',
'Mar',
12345.67
)
"
(Note - SQL is OK with linebreaks and the output can be easier to diagnose)

I personally don't bother with expand as it seems to me that it doesn't do anything you can't do by building the string yourself with | .

It's a tricky area, joining two environments together - neither will help you with the other. Good luck.

Posted: **Mon Sep 10, 2012 1:07 am**

I've got a similar issue, I'm trying to send the data from a text file to an SQL database.

Our current set up is using MS Access to import the text file and then "append" the new transaction data into our SQL database, we want to automate this via a TM1 process.
I seem to be able to generate the exact same SQL statement from Tm1 that I use in my Visual Studio but I get this error:
12004 ERROR 2012-09-10 00:59:16.543 TM1.Process Process "123 JC Trans Daily 3": : Could not initialize process

This is my code:

Code: Select all

#========= P R O L O G =========


# Open the ODBC Connection
ODBCOpen('TM1DBP_UAT', '', '');

#Define Variables
MyCube = 'z_ctrl_measures';

Period = CellGetS (MyCube , 'Current Trans Daily Date', 'String 1');


#=============== D A T A ===============


# Use ODBC link to Insert Trans_Temp table to the Transactions_Details table.
#Syntax:
# ODBCOutPut( 'ODBC Connection Name', Expand( 'INSERT INTO TableName ( Coulmn1,Coulmn2,Coulmn3,Coulmn4,Coulmn5, Coulmn6 )' | '
# VALUES (''%v1%'',''%v2%'',''%v3%'',''%v4%'',''%v5%'',''%v6%'',%v7%)'));



sqlStmt = Expand( 'INSERT INTO TRANSACTION_DETAIL (COY_NO , GL_ACCOUNT , RC, TRANS_DATE ,
TRANS_CODE , DESCRIPTION , TRANS_SOURCE , TRANS_REF , AMOUNT , LV , TRANS_DATE_EFF , SEQUENCE_NO ,
BATCH_NO , TM1_MONTH , FDL_B , TM1_Daily , TYPE , MAPA , MAPB , MAPC , MAPD , PRODPROC ,
AMOUNT_AVG  ) ' | ' VALUES (''%COY_NO%'' , ''%GL_ACCOUNT%'' , ''%RC%'', ''%TRANS_DATE%'' , ''%TRANS_CODE%'' , ''%DESCRIPTION%'' ,
''%TRANS_SOURCE%'' , ''%TRANS_REF%'' , %AMOUNT% , ''%LV%'' , ''%TRANS_DATE_EFF%'' , ''%SEQUENCE_NO%'' , ''%BATCH_NO%'' ,
''%TM1_MONTH%'' , ''%FDL_B%'' , ''%TM1_Daily%'' , ''%TYPE%'' , ''%MAPA%'' , ''%MAPB%'' , ''%MAPC%'' , ''%MAPD%'' , ''%PRODPROC%'' ,
%AMOUNT_AVG%  )') ;

# Remove the Quote Characters from the SQL
DatasourceASCIIQuoteCharacter='';

Asciioutput ( '\\aussydtm002\tm1\temp\sql Daily 3.txt', sqlStmt );

ODBCOutput('TM1DBP_UAT', sqlStmt ) ;

#========= E P I L O G =========

# Execute process for check to see if Balances send in the SQL query match to the balances in z_ctrl_measures cube
ExecuteProcess('123 JC Trans Daily 3a', 'cubename', 'z_ctrl_measures',
'z_ctrl_measures' , 'Current Trans Daily Date', 'z_string' , 'TM1_Daily' );

# Close the ODBC link to the SQL DB
ODBCClose('TM1DBP_UAT') ;

The result of the asciioutput is:

INSERT INTO TRANSACTION_DETAIL (COY_NO , GL_ACCOUNT , RC, TRANS_DATE ,TRANS_CODE , DESCRIPTION , TRANS_SOURCE , TRANS_REF , AMOUNT , LV , TRANS_DATE_EFF , SEQUENCE_NO ,BATCH_NO , TM1_MONTH , FDL_B , TM1_Daily , TYPE , MAPA , MAPB , MAPC , MAPD , PRODPROC ,AMOUNT_AVG ) VALUES ('2400' , '0222513000' , '1180', '120906' , '61' , 'TAX' ,'PT' , '00426664' , 116.180 , '52' , '120906' , '00001' , '9743' ,'Sep 2012' , '' , '06 Sep 2012' , 'FMS' , '' , '' , '' , '' , '' , 0.000 )

I have requested our Tech team to confirm that our TM1 Admin account has access to TM1DBP_UAT and they say it does (though not exactly sure on how to prove this).

Anyone have any suggestions?

Posted: **Mon Sep 10, 2012 2:47 am**

RJ! wrote:Our current set up is using MS Access to import the text file and then "append" the new transaction data into our SQL database, we want to automate this via a TM1 process.

This isn't 64-bit TM1 by chance is it? There is no 64-bit driver for Microsoft Access so you can't use it as a database when using the 64-bit version of TM1.

Posted: **Mon Sep 10, 2012 3:41 am**

Hi Tomok,

Just to confirm, we are not using MS Access as the "database", it is merely used to execute the code to append our transactions from the text file into our main SQL Database tables on a SQL Server 2008.

We are trying to eliminate that MS Access step and have a TM1 process execute the SQL to append the transaction file to the table in SQL Server, but get the error above

Posted: **Mon Sep 10, 2012 5:42 am**

Just another note, I have run the Process without the code to do the SQL update - ODBCOutput('TM1DBP_UAT', sqlStmt ) ;

I still get the same result... "Could not initialize process"

And just to confirm that I have access to the ODBC source, I can retrieve the transactions from our Drill process

Posted: **Mon Sep 10, 2012 7:55 am**

Can you check that the parameter names for the process that you are calling through ExecuteProcess are spelt correctly, and also the process name itself.

Posted: **Mon Sep 10, 2012 4:36 pm**

Try exporting the old values using the formula below. SQL is looking for the ‘ ‘.

New Value = ''''| Old Value|'''';

Posted: **Tue Sep 11, 2012 12:24 am**

Hi Jorel,

My code does have the single quotation marks...

BTW - found that the Could not Initialise Process was due to this line of code:

Code: Select all

# Execute process for check to see if Balances send in the SQL query match to the balances in z_ctrl_measures cube
ExecuteProcess('123 JC Trans Daily 3a', 'cubename', 'z_ctrl_measures',
'z_ctrl_measures' , 'Current Trans Daily Date', 'z_string' , 'TM1_Daily' );

Now the process completes successfully, though it is still not updating the Data into the SQL database.
I have actually pasted the SQL from my asciioutput into an SQL tool & the line updates perfectly, still no idea why it won't work from the TM1 process...

Posted: **Tue Sep 11, 2012 3:58 am**

ok, for sh!ts & giggles, i put in my username & password into the ODBCOpen and ran the process, then put in the WRONG password and it still process successfully without actually updating the SQL database

Shouldn't I get some error if i was really connecting to the ODBC source or is it ignoring the password in the code & using the system password that is already knows?

TM1 Forum

Exporting Cube data to Sql Server Table

Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table

Re: Exporting Cube data to Sql Server Table