ODBC ERROR-Not updating a sql table from cube view

[dilip]

Prolog part;
ODBCopen('ABC','xxxx','xxxx');

data part;
sql= 'INSERT INTO table1(SYS_IBP_UPLOAD_ID,YEAR,MONTH,DAC,PRODUCT,TARGET,UNIT,ERR_MSG,PROCESSING_STATUS,LOAD_DATE,STATUS)

values ('|numbertostring(v19)|','''|v7|''','''|v5|''','''|v4|''','''|v1|''','|numbertostring(Value)|','''|v8|''','''|v20|''','''|v21|''','''|v22|''','|numbertostring(v23)|')';



ODBCOutput('ABC',sql);

epilog part;
odbcclose('ABC');

I have written above odbc connection to output the data from tm1cube view to sqldatabase.ABC is my DSN name and the server is '10.0.12.xx\poosd'. when iam giving the dsn name ABc in ODBCopen it runs succeefully but value is not coming in sql database but when iam using sqlservername i.e 10.0.12.xx\poosd instead of DSN then it is saying unable to find the datasource.

please let me know where iam doing a mistake.

[Duncan P]

Where did you get the idea that ODBCOpen would accept a SQLServer database identifier instead of an ODBC datasource name? It only takes a datasource name. The documentation is quite clear.

You say that when you use the datasource name no values are inserted into the table. Have you tried to trace the ODBC connection? If so then perhaps you could attach the trace file.

[dilip]

This error iam getting it is saying that iam not able to open dsn1.

Error: Prolog procedure line (5): Unable to open data source: "dsn1"
8080 [4] ERROR 2013-08-23 16:16:51.847 TM1.SQLAPI 08003[Microsoft][ODBC Driver Manager] Connection not open
8080 [4] ERROR 2013-08-23 16:16:51.847 TM1.SQLAPI E16) Cannot connect to ODBC data source "dsn1" IM003Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (Oracle in XE, C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqora32.dll)..

any suggestion?

[Duncan P]

Have you checked that you can use the datasource from within another client, such as Excel? I you cannot use it from within Excel then the problem is with the datasource itself and nothing to do with TM1, and you need to go to Oracle or Microsoft information sources to find out why.

BTW what you have just posted is not consistent with your original post. Does your ABC datasource still open and are you still seeing the original problem?

[dilip]

That ABC problem still persist I tried to trace but in the txt file iam nt getting anything,then after that tried to load via text file to sql through tm1 then the eroor was same as i posted that ODBC driver error some thing of that type ora driver. till now same error there also iam getting which i have posted just now i.e ODBC driver error.

[dilip]

I have checked with excel client also but saying the same error that is [Microsoft][ODBC Driver Manager] Connection not open

Cannot connect to ODBC data source "dsn1" IM003Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization

I have checked everything connection is successfull but why it is not connecting to DSN, its a big problem if this problem solved then ABC problem will also be solved.

But thank you Duncan for your ideas...

[declanr]

Dilip,

The error you are receiving has nothing to do with TM1.

The ODBC driver has either been setup incorrectly, been corrupted or is simply the wrong one.

In simple terms you should find out exactly what driver is required for the job (possibly see if the database is connected to for reasons on another server and replicate the setup) and install that. If the error still persists you will find plenty of information on the error message you provided by googling it.

Sometime you can need to manually register dll files if the setup has become corrupted for various reasons but I would expect it simply requires a reinstall of the driver (specifically correct driver.)

[dilip]

Thnk you all..the issue is solved.....its solved by using table name correctly with the database name and with proper syntax......for eg insert into table "database"."tablename"................it was not the driver issue....