Call to SQLFetch failed

jshurak · Post by **jshurak** » Mon Feb 20, 2006 10:49 am

I'm receiving an error message: Extract_Daily_Balance_Status..CTransformerStage1.prod2BLINin_DAH: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT trim(CHPLNM), CHPFNM, CHSLNM, CHSFNM, CHADD1, CHADD2, CHCITY, CHSTAT,CHZIP1, CHZIP2, "CHACT#", CHCYCC,CHEDTE, CHESTS, CHISTS, CHCRDL, CHAVAL, CHHPHN, CHBPHN, "CHSSN#", CHSCHG, CHMDUE, CHDEQD,CHCURR, CHDDEQ,CHCURP, CHRPT1,CHRPT2, CHPORT,CHSTRC, CHADCD, CHMSC9, CHPAYD, CHMSC1, CHMSC2, CHMSC3, CHMSC4, CHMSC5, CHMSC6, CHMSC7, CHMSC8, CHMS10, CHTYLS, CHFRCD, CH58SF, CHHOLD, CHCNDT, CHLIFE, CHCOCD,CHCOAM,CHODTE,CHLPAY,CHCOD,CHFIXD,CHPRCD,CHDSTA,CHSBAL,CHRNEW FROM ACSPRDDTA.BLIN060219

The next stage gives me this message:
Extract_Daily_Balance_Status..CTransformerStage1: |19|

I'm not sure what the ' |19|' is.

This is an old job that we recently added new columns to. It ran fine the first time it was scheduled after the update. I've matched all the metadata but this job keeps failing.

kcbland · Post by **kcbland** » Mon Feb 20, 2006 11:00 am

Can you please paste all yellow and red messages? This isn't enough information, but the indications are malformed SQL. Is this custom SQL? Have you tested the SQL outside of DS? Do any rows return, meaning does the job run for awhile and then have issues?

jshurak · Post by **jshurak** » Mon Feb 20, 2006 11:22 am

I'm copying this from datastage director, i'm not seeing any yellow or red messages, this will return over 1 mil rows, which is normal. It gets to towards the end of the extract, then fails

kcbland · Post by **kcbland** » Mon Feb 20, 2006 11:45 am

Since I don't know your job design, consider doing the following. Put an @FALSE constraint in the first transformer after the ODBC stage. This will cause the job to run and have all rows transferred from the source database to the transformer and then be thrown away. If the query completes successfully, then the problem is after the ODBC stage. If the job fails again, the issue is between the database and the ODBC stage.

If the ODBC stage is the issue, you need to figure out the data that is causing the problem. Add a ORDER BY clause to get the data in a consistent order. Consider writing the output of the ODBC stage to a sequential file and run it until it blows up. The last row written tells you the next row to be acquired, which would be the one causing the problem.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 20, 2006 2:31 pm

Does the job abort? If so, reset the job in Director and let us know whether there is any message "from previous run..." (and post its contents).

Are you certain that it ran successfully once after the modification? That is, did you accidentally run the old version before compiling the new?

jshurak · Post by **jshurak** » Mon Feb 20, 2006 2:36 pm

There is a chance that this might be a problem in the source table. I've searched for other posts of the 'CTransformerStage1: |xxx|' error. does anyone know what is in the pipes?

jshurak · Post by **jshurak** » Wed Feb 22, 2006 9:02 am

I_Server_Whale · Post by **I_Server_Whale** » Wed Feb 22, 2006 9:14 am

Hi jshurak,

What do you mean by BUMP??? Ray had asked you a very good question. You seem not interested in providing more information about your job and the information that Ray asked, inorder for us to better understand your situation.

You said in your first post that the job fails. And a when a job fails you will have 'warning' and 'fatal' messages in your director log.

Please post them if you need us to post a better solution or atleast we can think about what exactly is happening.

Thanks,
Naveen.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 22, 2006 4:07 pm

jshurak wrote:BUMP

I find that totally offensive. I asked you reasonable questions to help to diagnose your problem, you have not answered them, and you are demanding answers. Also, we are people, not pinball machines.

chulett · Post by **chulett** » Wed Feb 22, 2006 4:15 pm

Posting a 'BUMP' message is a mechanism used by people on other web-boards to get their issue 'bumped' back up to the top of the forum when they have nothing else to add and are just waiting for someone to notice.

Not really appropriate here.

jshurak · Post by **jshurak** » Fri Feb 24, 2006 8:38 am

Ray,

I am very sorry you found that offensive. It was not my intention to offend anyone by posting the "bump," for that I am sorry. It was in no way intended on in an offensive way. I simply posted it to try and receive an answer to the question in the previous post. To answer your questions. Yes I am certain that it ran successfully the day before and has run successfully every day since. The only day it aborted was on the 19th. If you read further down this post you will find the answer to your call for the error messages to be posted.

To Naveen,

As I said in the original post, yes the job gave me a warning and a fatal error, which is mentioned in the original post.

I'm receiving an error message: Extract_Daily_Balance_Status..CTransformerStage1.prod2BLINin_DAH: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT trim(CHPLNM), CHPFNM, CHSLNM, CHSFNM, CHADD1, CHADD2, CHCITY, CHSTAT,CHZIP1, CHZIP2, "CHACT#", CHCYCC,CHEDTE, CHESTS, CHISTS, CHCRDL, CHAVAL, CHHPHN, CHBPHN, "CHSSN#", CHSCHG, CHMDUE, CHDEQD,CHCURR, CHDDEQ,CHCURP, CHRPT1,CHRPT2, CHPORT,CHSTRC, CHADCD, CHMSC9, CHPAYD, CHMSC1, CHMSC2, CHMSC3, CHMSC4, CHMSC5, CHMSC6, CHMSC7, CHMSC8, CHMS10, CHTYLS, CHFRCD, CH58SF, CHHOLD, CHCNDT, CHLIFE, CHCOCD,CHCOAM,CHODTE,CHLPAY,CHCOD,CHFIXD,CHPRCD,CHDSTA,CHSBAL,CHRNEW FROM ACSPRDDTA.BLIN060219

The next stage gives me this message:
Extract_Daily_Balance_Status..CTransformerStage1: |19|

The "call to SQLFetch" was the warning and the "CTransformerStage1: |19|" message was the fatal error. I should have specified that in the original message, I simply assumed that would be understood. I should have learned my lessons about assumption a long time ago, and for that I apologize.

jshurak · Post by **jshurak** » Fri Feb 24, 2006 8:39 am

Ray,

I am very sorry you found that offensive. It was not my intention to offend anyone by posting the "bump," for that I am sorry. It was in no way intended on in an offensive way. I simply posted it to try and receive an answer to the question in the previous post. To answer your questions. Yes I am certain that it ran successfully the day before and has run successfully every day since. The only day it aborted was on the 19th. If you read further down this post you will find the answer to your call for the error messages to be posted.

To Naveen,

As I said in the original post, yes the job gave me a warning and a fatal error, which is mentioned in the original post.

I'm receiving an error message: Extract_Daily_Balance_Status..CTransformerStage1.prod2BLINin_DAH: DSD.BCIGetNext call to SQLFetch failed.
SQL statement:SELECT trim(CHPLNM), CHPFNM, CHSLNM, CHSFNM, CHADD1, CHADD2, CHCITY, CHSTAT,CHZIP1, CHZIP2, "CHACT#", CHCYCC,CHEDTE, CHESTS, CHISTS, CHCRDL, CHAVAL, CHHPHN, CHBPHN, "CHSSN#", CHSCHG, CHMDUE, CHDEQD,CHCURR, CHDDEQ,CHCURP, CHRPT1,CHRPT2, CHPORT,CHSTRC, CHADCD, CHMSC9, CHPAYD, CHMSC1, CHMSC2, CHMSC3, CHMSC4, CHMSC5, CHMSC6, CHMSC7, CHMSC8, CHMS10, CHTYLS, CHFRCD, CH58SF, CHHOLD, CHCNDT, CHLIFE, CHCOCD,CHCOAM,CHODTE,CHLPAY,CHCOD,CHFIXD,CHPRCD,CHDSTA,CHSBAL,CHRNEW FROM ACSPRDDTA.BLIN060219

The next stage gives me this message:
Extract_Daily_Balance_Status..CTransformerStage1: |19|

The "call to SQLFetch" was the warning and the "CTransformerStage1: |19|" message was the fatal error. I should have specified that in the original message, I simply assumed that would be understood. I should have learned my lessons about assumption a long time ago, and for that I apologize.

jshurak · Post by **jshurak** » Fri Feb 24, 2006 8:39 am

I accidentally posted that twice

chulett · Post by **chulett** » Fri Feb 24, 2006 8:50 am

You should have removed the second post then.

Only you can do that. However, now that there is a reply after it (yours!) that option is no longer possible. Something to remember for the future.