Write data from TM1 to MSSQL2000 via ODBC

[anote]

I'm trying to create TI for update data back to MSSQL but it doesn't work.

Here is my TI script.

on Data tab

ODBCOpen('TM1MSSQL','xxx','xxx');

ODBCOutput('TM1MSSQL', INSERT INTO Season_Index ( Version, Year, Month, Entity, Assumption_Measure, Percent_Inc ) VALUE("%Version%","%V2%","%V3%","%Entity%","%Assumption_Measure%", %Percent_Inc% )' );

ODBCClose('TM1MSSQL');

This process run completed successfully but there is no update on my MSSQL200 in Season_Index table

Can anyone give me a suggestion?

[David Usherwood]

Looking at your script:
ODBCOutput('TM1MSSQL', INSERT INTO Season_Index ( Version, Year, Month, Entity, Assumption_Measure, Percent_Inc ) VALUE("%Version%","%V2%","%V3%","%Entity%","%Assumption_Measure%", %Percent_Inc% )' );

I don't think TI will handle the %V2% etc references properly. The proper way is to use EXPAND, but I found that building a text string using | worked fine too. NB to put ' into such a string, you need to double it eg ''''' will give you one ' .

In the end, ODBCOUTPUT sends a text string to SQL to be executed. Try putting your SQL command into a text variable and then ASCIIOUTPUT-ing it. That will help to get the syntax right.

HTH - but I haven't validated this explicitly just now. Just my possibly failing memory of how I did it.