CODEWITHSUNDEEP
sqlexcelfunctiondb2excel-udf
Thevagabond
Thevagabond

Reputation: 323

SQLSTATE=39501 while using UDF with Excel for DB2

I am trying to get data from excel into a db2 database to then export that to ixf.

Here is the source (ddl file)

connect to MYDB

drop function ARTIKEL_MATNR;



CREATE FUNCTION ARTIKEL_MATNR()
RETURNS TABLE(Material VARCHAR(64), HerstellerteileNr VARCHAR(32), Hersteller VARCHAR(5))
LANGUAGE OLEDB
EXTERNAL NAME '![IH09_Material$]!Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\excel_imp\150202_Bestand_SASPF_GBP.xls; Extended Properties="Excel 8.0;HDR=YES"';



select count(*) FROM TABLE(ARTIKEL_MATNR()) AS T;

INSERT INTO QV_ARTIKEL_MATNR (MATNR, TKZ, HSTK)
SELECT SUBSTR(Material,1,64), SUBSTR(HerstellerteileNr,1,32), SUBSTR(Hersteller,1,5) FROM TABLE(ARTIKEL_MATNR()) AS T;

select count(*) from QV_ARTIKEL_MATNR;


EXPORT TO C:\excel_imp\ARTIKEL_MATNR.ixf OF IXF MESSAGES C:\excel_imp\msg.txt SELECT * FROM  QV_ARTIKEL_MATNR;

However I get 

SQL0450N Routine "scheme.ARTIKEL_MATNR" (specific name "SQL15020610250178") has generated a result
value, SQLSTATE value, message text, or scratchpad which is too long. SQLSTATE=39501

I already enlarged all the buffers of protocolls and so on but the problem still remains. The Excel file is about 190k rows long.

If I only do a select on the function returned table the problem is there already, so the insert is not even reached.

Any ideas? Thanks for all your help in advance.

Upvotes: 0

Views: 222

Answers (1)

mustaccio
mustaccio

Reputation: 19001

First thing to check might be that none of the Excel cells contains values that are longer than declared columns in the table function. Remember, column length is specified in bytes, not characters. If your Excel file contains non-ASCII characters a 32-character string might be longer than 32 bytes.

Also see if db2diag.log contain more information at the time of error.

Upvotes: 1

Related Questions