Getting only the first character of a string as SP output

[kaushal.kumar@igate.com]

We are calling a Stored Procedure from an ODBC stage. When we execute the SP in SQL Server, it should give the output as a single column having multiple rows of comma dilimited strings as shown below:
COLUMN1
tttttttttttttt,59779597,kjjjjjjjjjj
jjjjjjjjj,iiiiiiiiiiiiii,999999999
ddddddddd,88884224,bbbbbbb
rrrrrr,0987993,fjkghklshl

But when we call the SP from Datastage, then we are getting the output as shown below:
COLUMN1
t
j
d
r

That is only the first characted of the row.

[ArndW]

I assume that you have tested your stored procedure in SQL server directly and get correct contents returned, so the first question would be how you have declared your datatypes.

[kaushal.kumar@igate.com]

I assume that you have tested your stored procedure in SQL server directly and get correct contents returned, so the first question would be how you have declared your datatypes.

Actually, the output of the SP is a select stmt from a table based on some conditions. Hence we need not define any datatype since there are no output variable.

[chulett]

... and yet you need to get output? Hmmm...

[asorrell]

I'll be more specific than Craig... What is ok for output to a screen is NOT ok for output to another process. You need to modify the stored procedure and add output parameters to pass the data back in a structured format. Those parameters must be specifically declared and typed.

I would then recommend using the Stored Procedure stage (if you are on 8.1 - it was buggy at 8.0 and may need patches). If you do so, you can then setup your stored procedure stage as a either a transform type (if you send it info before it runs) or a source type (if all it does is return info). Don't forget to check "procedure returns multiple rows" on the output. You'll also need to define column metadata that matches the output parameters with the exception that the first character cant' be an @-sign.

Last note - if you use the stored procedure stage, your stored procedure can't output anything to the screen in a PRINT statement or a WARNING statement or it will cause DataStage to have problems returning the parameters. I've reported this as a bug to IBM about a week ago and I'm still waiting to see their response.

[chulett]

Thanks Andy, I had to get out the door and have zero internet access during the day, so figured someone would have my back.