DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
abyss



Group memberships:
Premium Members

Joined: 22 May 2014
Posts: 155

Points: 1615

Post Posted: Fri Oct 06, 2017 4:34 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hi All:
I have a job extract data from odbc stage (connect to sql server) and output the result to a sequential file

job failed with the following error logs:

Code:
ODBCEvents: Connected to Microsoft SQL Server, version 12.00.4100 through driver <driver>.DLL.
The driver does not support quoted identifiers in SQL statements
External schema field, name: <field name>, type: INT32 was not found in the design schema
Design schema field, name: <field name>, type: INT32 was not referenced in the SQL statement
Error when checking operator: Could not find input field "<field name>".
main_program: Could not check all operators because of previous error(s)
main_program: Creation of a step finished with status = FAILED.


does anyone know why?

cheers
abyss
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42163
Location: Denver, CO
Points: 216469

Post Posted: Fri Oct 06, 2017 6:56 am Reply with quote    Back to top    

Doesn't that error message say exactly why? Sure looks like it does to me. And it doesn't have anything to do with creating a sequential file, looks to be all about the ODBC source extract.

_________________
-craig

Dr. Frankenstein entered a bodybuilding competition and discovered he had seriously misunderstood the objective.
Rate this response:  
Not yet rated
abyss



Group memberships:
Premium Members

Joined: 22 May 2014
Posts: 155

Points: 1615

Post Posted: Sat Oct 07, 2017 12:55 am Reply with quote    Back to top    

hmmmmm, i fixed problem by following exactly what you said.

the select statement has one field name mismatch with output table definitions name.
thanks, and this is lead to another 2 questions:
1. why sql field name have to match with table definition for ODBC stage? from my experience as long as their positions are match, the stage should work.
2. if i change the sql stage to a peek stage, the old job still works, why?

anyway, thanks very much!
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54007
Location: Sydney, Australia
Points: 293017

Post Posted: Mon Oct 09, 2017 3:21 am Reply with quote    Back to top    

Your stated experience about order relates only to parameter markers, and these are not used in SELECT statements. In SELECT statements you must precisely what values you wish to see, either via ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54007
Location: Sydney, Australia
Points: 293017

Post Posted: Mon Oct 09, 2017 3:22 am Reply with quote    Back to top    

The answer to #2 is probably that you did not compile the changed job, so that the executable is still the compiled version of the original job.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours