DSXchange

I was trying to insert data into excel file using ODBC stage, somehow i'm getting the following error..
SQLSTATE=S1000, DBMS.CODE=-3035
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Operation must use an updateable query.

My SQL is INSERT INTO "Sheet1$"(COL1, COL2) VALUES (?,?);

Does anybody inserted data into excel file through ODBC,please advise.

You can use sequential stage and create the file as .csv. This will create a EXCEL spread sheet.

This should work with ODBC - What does the SQL command look like if you use auto-generated commands?

ArndW,

ArndW wrote:This should work with ODBC - What does the SQL command look like if you use auto-generated commands? ...

I have used update action as insert rows without clearing and generated SQL is
INSERT INTO `Sheet1$`(COL1, COL2) VALUES (?,?);

Thanks

nivas wrote:You can use sequential stage and create the file as .csv. This will create a EXCEL spread sheet.

fyi.. xls format is not same as .csv

Does the Sheet1 in question have column headings COL1 and COL2 ?

Using Sequential File stage if you create a file with extention .csv and select the check box First Line is column names, delimiter as "," there by it will create a spread sheet with first row as column names and the rest rows with data.

Nivas - a .csv file is not the same as a .xls file; the OP is using ODBC to populate the Excel sheet directly.

ray.wurlod wrote:Does the Sheet1 in question have column headings COL1 and COL2 ? ...

Yes, sheet1 has column headings COL1,COL2

Can you read from it using COL1 and COL2 in a test job?

ArndW wrote:Can you read from it using COL1 and COL2 in a test job? ...

Yes..i am able to read data this and inserted into sequential file without errors

Thanks for your help

So if you can read from the Excel ODBC then the connection and sheet format is correct. Is your write SQL automatically generated or user-defined?

I have tried both generated SQL and user defined SQL but i got the same error.
SQLSTATE=S1000, DBMS.CODE=-3035
[DataStage][SQL Client][ODBC][Microsoft][ODBC Excel Driver] Operation must use an updateable query.

Thanks..

Does "UPDATE" work?

You might want to look into the following article at Microsoft:

http://support.microsoft.com/kb/175168

John