Calling SQL Server UDF (User Defined Function) from DS

[craviraj]

Our job needs to call a UDF through DataStage and the UDF takes 3 parameters that will change based on the input. The UDF returns 0 or more rows for each value passed. We have to take one path if the UDF returns 0 rows and if the UDF returns more than 0 rows, we have to use the values and do some transformations.

One thing to note is that the number of times the UDF is called is not constant. For eg. if the input contains 10 records, UDF should be called 10 times and act on the output in the way as explained above for each of these 10 records.

The design that we followed is:
Read the file as input and do a sparse lookup to call the UDF for each of the records from input. Lookup Mode=Continue; so if the UDF does not return any row, it can still pass with the UDF returned values assigned to 0 (Not Null fields) and if the UDF returns any value - the data fetched (may be multiple rows per input) would be used for transformations.


Questions:
1) Will this design work?
2) When testing with just one record, the job aborts stating that the multipart field cannot be taken as input. [The parameter is a GUID that contains hyphen - in the text).
3) The datatype is SQL Variant and for returning this datatype - datastage is throwing an error "Datatype not supported". How to read this?

This is a very urgent requirement. Please help.

Thanks,
Raviraj

[craviraj]

Updates:

The job ran fine with 1 record. When 1 try to run the job with 2 records, it aborted with the following error:

[Microsoft][SQL Native Client][SQL Server].NET Framework execution was aborted by escalation policy because of out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
Syste

Any ideas?



I have handled 2 and 3 by using convert(varchar(50),<FieldName>).

2) When testing with just one record, the job aborts stating that the multipart field cannot be taken as input. [The parameter is a GUID that contains hyphen - in the text).
3) The datatype is SQL Variant and for returning this datatype - datastage is throwing an error "Datatype not supported". How to read this?

[chulett]

I have handled 2 and 3 by using convert(varchar(50),<FieldName>)

If that is the DataStage convert() function that's not how it is used.

For the 'SQL Varient' can you cast it to its base data type in the select?

[craviraj]

I have handled 2 and 3 by using convert(varchar(50),<FieldName>)

If that is the DataStage convert() function that's not how it is used.

For the 'SQL Varient' can you cast it to its base data type in the select?

It is not DataStage convert; it is the SQL variant and the command works. I have used it in the SQL Server Enterprise Stage.

[ray.wurlod]

Try using CAST rather than CONVERT.

[craviraj]

The design is working as expected