Error when running Data stage server job

[sashah]

I am trying to run a job that has a reference link. There is an ODBC stage that has a reference link to the transformer. This stage returns multiple rows for a particular id which I need to either insert or update into the database. I get the below error when running the job.

DSD.BCIOpenRef call to SQLNumResultCols failed.SQL statement has incorrect number of result columns

Can someone please help.

Thank you
Samir Shah

[mikegohl]

Do you have the "Reference link with multiple row result set" box checked?

[sashah]

Micheal,

Thank you for your reply.

Where do I find multiple results check box to check it.

Thank you
Samir

[mikegohl]

You will find it in the transform. Stage properties for the reference link.

[chulett]

That's not the issue, it says you're using user-defined sql and you're not selecting the same number of columns as you've defined in the stage.

[mikegohl]

Good catch. I should read the entire post.

[sashah]

Hi Craig,

Thank you for your response.
I verified the number of result columns defined in the stage. They match the number of columns in the query.
Is there something else that I should verify.

Thank you
Samir

[sashah]

Hi Craig,

Thank you for your response.
I verified the number of result columns defined in the stage. They match the number of columns in the query.
Is there something else that I should verify.

Thank you
Samir

[chulett]

Can you post the sql you are using? That and verify that any of the columns used in the 'where' clause are marked as Key fields as well.

[sashah]

Hi Craig,

There are no where clauses. I am calling a function in SQL Server that is returning data.

Below is the sql from Select Account_nsp_id,BENCHMARK_nsp_id,SEQUENCE_NUM,PRIMARY_BENCHMARK from V_Account_Benchmarks('ABC',?)

Thank you for your help.

Regards
Samir

[sashah]

Hi Craig,

There are no where clauses. I am calling a function in SQL Server that is returning data.

Below is the sql from Select Account_nsp_id,BENCHMARK_nsp_id,SEQUENCE_NUM,PRIMARY_BENCHMARK from V_Account_Benchmarks('ABC',?)

Thank you for your help.

Regards
Samir

[ray.wurlod]

Why is there a parameter marker? Because there is an implied WHERE clause.

[chulett]

Is one of the four columns being selected the same column substituted in for the parameter marker? Four columns selected, four columns defined in the stage? Or five?

[sashah]

Yes one of the four column selected is substituted in the parameters marker. The marker is for column Account_nsp_id. 4 columns are defined in the stage.

[sashah]

Yes one of the four column selected is substituted in the parameters marker. The marker is for column Account_nsp_id. 4 columns are defined in the stage.