Excel: An unsupported SQL data type was encountered

mgsteiner · Post by **mgsteiner** » Fri May 11, 2012 5:36 am

I got the same error as Sonia in this post and cannot find any workaround.

@Sonia, can you please put the solution you have found to solve this issue?

ray.wurlod · Post by **ray.wurlod** » Fri May 11, 2012 5:40 am

It may be that you have an ODBC driver for Excel, even though Excel itself is not installed. In that case you can use an ODBC stage.

mgsteiner · Post by **mgsteiner** » Fri May 11, 2012 5:47 am

ray.wurlod wrote:It may be that you have an ODBC driver for Excel, even though Excel itself is not installed. In that case you can use an ODBC stage. ...

I'a currently using an ODBC Stage, but I get this error and I have tried to change the data type in the ODBC Stage but the problem remains.

The full error is the following:

Ext2Excel..Transformer_1: |Ext2Excel..ODBC_0.DSLink2: DSD.BCIOpenW call to function SQLPrepare failed.
SELECT MESSAGETYPEID,MESSAGESEVERITY,MESSAGETEXT FROM `Tabelle1$`
SQLSTATE=S1004, DBMS.CODE=0
[DataStage][SQL Client]An unsupported SQL data type was encountered|

chulett · Post by **chulett** » Fri May 11, 2012 6:26 am

That thread was eight years old and Sonia hasn't posted in four. Off to your own post.

The original post was for Parallel jobs but your posts are always for Server jobs... is this a Server job question as well?

mgsteiner · Post by **mgsteiner** » Fri May 11, 2012 6:38 am

Yes . It is server job . DataStage 8.1.2

chulett · Post by **chulett** » Fri May 11, 2012 7:04 am

Thanks... corrected and in the proper forum now.

Kryt0n · Post by **Kryt0n** » Sun May 13, 2012 4:28 pm

What format types have you specified within the Excel sheet?
Try make all columns (at least those you're extracting anyway) type Text (within Excel) and see if it works

mgsteiner · Post by **mgsteiner** » Sun May 13, 2012 10:51 pm

No , it doesnt work. Always I receive the same error

Ext2Excel..ODBC_0.DSLink2: DSD.BCIOpenW call to function SQLPrepare failed.
SELECT MESSAGETYPEID FROM `Tabelle1$`
SQLSTATE=S1004, DBMS.CODE=0
[DataStage][SQL Client]An unsupported SQL data type was encountered

Any other tips?

Kryt0n · Post by **Kryt0n** » Sun May 13, 2012 11:38 pm

Yeah, forget Excel... right royal pita!

Do a table import of your ODBC DSN and use the definition it creates in your job.

While I would truly hope it isn't the cause, you could also try

Code: Select all

SELECT `MESSAGETYPEID` FROM `Tabelle1$`

mgsteiner · Post by **mgsteiner** » Mon May 14, 2012 2:23 am

I have done an ODBC Table Definition Import in DS and used in ODBC Stage to write to it, in oder to have an Excel file with the data from my Hash Table.

Hash_Table --> Transformer --> ODBC Stage (for Excel)

Anyhow can't get it work due to the Data Type error

For what do you suggest a Select stmt when I want to Insert?

Kryt0n · Post by **Kryt0n** » Mon May 14, 2012 3:42 pm

Not much really, we had to fiddle around a bit ourselves so can't be sure which bits were right. Did find it liked those funny quotes though (the one below the ~), just make sure the column names match the first row of the excel sheet (your Tabelle1 is the name of a sheet rather than a named section I assume?)

sreewin7 · Post by **sreewin7** » Mon May 14, 2012 4:23 pm

Try to check your odbc drive connection and if your execl may be correct, delete and try to do one more time.

mgsteiner · Post by **mgsteiner** » Mon May 14, 2012 10:40 pm

Thanks a lot for all suggestions. I will try out and see if I can make it work. The, I will post my results.

Anyhow please, let me know if anybody has any other hint.

DSXchange

Excel: An unsupported SQL data type was encountered

Excel: An unsupported SQL data type was encountered

Re: Excel: An unsupported SQL data type was encountered