DSXchange

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

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

Micheal,

Thank you for your reply.

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

Thank you
Samir

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

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.

Good catch. I should read the entire post.

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

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

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.

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

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

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

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?

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.

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.