DSXchange

I am using ODBC stage and trying to write to an excel file. The DSN is mapped to excel and the worksheet chosen as the table. But when trying to write to the excel


Event #:504
Timestamp:8/12/2004 3:58:15 PM
Event type:Warning
Message:
Accountxls..ODBC_4.Lnk_Account: DSD.BCIOpenR call to SQLExecDirect failed.

Statement was:SELECT HEADER, VERSION, ACCT_NUMBER accountloader

SQLSTATE=S1004, DBMS.CODE=0
[DataStage][SQL Client]An unsupported SQL data type was encountered

all the three columns are varchar.

what i do not understand is
1. why is the DS director giving me a select statement when the SQL stmt. is INSERT INTO accountloader(HEADER, VERSION, ACCT_NUMBER) VALUES (?,?,?)

2. "An unsupported SQL data type was encountered". Is this because the datatype i mentioned was Varchar or is it because the data was not compatible with the type.

thanks
Sonia

If excel is not installed in my DS server, would it pose a problem in writing to an excel?

Excel is probably reporting the column data type as NVarChar. Can't remember the workaround for this; do a search for NVarChar on the forum.

Finally I was able to write to excel. :D

But to drop table/worksheet before load or clear the table/worksheet before load does not seems to be working. It throws errors like

• DSD.BCIOpenW call to OCONV failed. Statement: DROP TABLE accountloader
  
  Deleting data in a linked table is not supported by this ISAM

I am creating the excel worksheet for each of my load, and it does not give any "object already existing" errors. So I guess Iam all set.

THANKS A LOT

Hi Sonia,
You can also use a Exec sh or Exec dos commands and give the pamareter as "rm file.xls" this will run before the your job runs and deletes that file using OS.
HTH
Thanks
Neena

Hi Neena,

My understanding of using Excel as a source and/or target was that the DSN points to the WorkBook (.xls file) as it does to a database and each sheet with the workbook is considered as a table.

In short

Database = Workbook (File.xls)
Table = Worksheet (sheet1)

Now If i remove the .xls file then I would get a error while the DSN tries to connect to the .xls file.

Do correct me if I am wrong

Thanks
Sonia

Sorry sonia,
I thought something else..
The scenario i gave you doesn't work for you..
Hope you got your solution already...
Thanks
Neena