ODBCOutput TI error

[tosca1978]

Hi,

I have the following TI set up to export the cell annotation to a sql table:

Data source: Cube view of }CellAnnotations cube
Variables: as per the cube plus 4 extra that scan the value to find the creator name, timestamp and comment etc. All variables set to other.
Advanced Prologue:

Code: Select all

ODBCOpen('COLUMBUS_TM1_PUBLISH','TM1_ADMIN','admin');

Advanced Data:

Code: Select all

AsciiOutput('D:\_BI\TM1_Application\TM1_Production\Output\Cell Annotation\Annotation', vMonth, vProbability,  vGrossNet, vName, vProductionType, vVersion, vMeasure , vCreator, vTimeCreated, value, vComment);

value = STR(CellGetN('Forecast Input by Month',vMonth,vProbability,vGrossNet,vHierarchy,vProductionType, vVersion,vMeasure), 4, 2);

ODBCOutput('COLUMBUS_TM1_PUBLISH', Expand( 'INSERT INTO [COLUMBUS_TM1_PUBLISH].[dbo].[PRODUCTION_ANNOTATION] ([Month], [Probability], [Hierarchy], [Gross or Net], [Production Type], [Version], [Measure], [Creator], [Time Created], [Value], [Comment]) VALUES ("%vMonth%", "%vProbability%", "%vName%", "%vGrossNet%", "%vProductionType%", "%vVersion%", "%vMeasure%" , "%vCreator%", "%vTimeCreated%", "%value%", "%vComment%")'));

The AsciiOutput works fine.

the ODBCOutput results in the TI aborting with the following error:

Data Source line (1) Error: Data procedure line (42): Error Executing SQL query: "..."

Not the most informative error message.

If I copy and paste the "INSERT INTO..." into SQL it works but only if I replace the "% with '

Any advice would be much appreciated.

Many thanks

[asutcliffe]

Is your relational database expecting single quotes rather than double quotes? You can insert a single quote by using two single quotes in your string. See - http://www.tm1forum.com/viewtopic.php?f=3&t=4929

[RJ!]

I thought I'd piggyback on this tread, I'm getting the same error with my SQL, code per below:

Code: Select all

ODBCOutput('TM1DBP_SQL', 
Expand( 'INSERT INTO TRANS_Details (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%  )')) ;

It gives me the error:

"0","2400","0555918000","2445","20120724","_","_","_","ACT","0","28","20120724","_","_","Jul 2012","_","24 Jul 2012","FDW","_","_","_","_","_","-628.74",Data Source line (1) Error: Data procedure line (20): Error executing SQL query: "..."

Note I have inserted "_" in to those fields just to make sure that it wasn't relating to null values etc.

I'm connecting to an MS SQL Server and all fields are Text except the 2 relating to Amount and the ID is an Autonumber which I've defaulted to be "0" if that helps...

I also tried with single quotes, double single quotes & double quotes, all to the same result (except Single Quotes which gave me a paretheseseses error)

[Duncan P]

You need to ASCIIOutput the result of the EXPAND function to see what it is actually generating. Then paste that into a query window in SQL Server to see what error it gives you.

e.g.

Code: Select all

sqlStmt = EXPAND( ... );
ASCIIOutput( 'sql.txt', sqlStmt );
ODBCOutput( 'TM1DBP_SQL', sqlStmt );

[RJ!]

The Result of the ASCIIOutput is:

"INSERT INTO TRANS_Details (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' , '0555918000' , '2445', '20120725' , '_', '_' ,'_' , 'ACT' , 0.000 , '28' , '20120725' , '_' , '_' ,'Jul 2012' , '_' , '25 Jul 2012' , 'FDW' , '_' , '_' , '_' , '_' , '_' , -603.590 )"

I'm assuming that the "space" between the numbers for Amount & Amount_AVG is what is causing the issue?
(Note that dispite what is shown above, there are 6 preceeding Spaces and 2 trailing Spaces with the Amount Value & the Amount_AVG value has 2 Preceeding & trailing spaces.)

This is the same string as it sitsin the text file I'm importing:

"2400","0555918000","2445","20120725","_","_","_","ACT","0","28","20120725","_","_","Jul 2012","_","25 Jul 2012","FDW","_","_","_","_","_","-603.59"

There are no spaces here, not entirely sure where the spaces are coming from...

I tried using Trim on those fields to try and strip the extra spaces and get the same result
Any other way to strip those characters from the SQL?

[RJ!]

I seem to have found most of my issue, the "TRANS_Details" is an alias I created in the Data Source, so renamed it to its real name and inserted the [] around it and now seems to export now

BTW - Was lucky it didn't work previously, I was actually pointing to our Prod DB & not UAT