OLE DB DateTime Format.

[dhiraj]

Hi,

I have a datastage job which reads from a seq file ,looks up in to an OLEDB stage (based on a character field and a date time field) and writes the looked up value to an output seq file. THe OLEDB has been set up for an SQL server 2000.

When ever i run the job i get the following error message.

DataStage Job 716 Phantom 3540
Program "JOB.1944410211.DT.1336655842.TRANS1": Line 50, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage OLEDBTEST..Transformer_1
DataStage Phantom Aborting with @ABORT.CODE = 3

The job runs fine if i remove the date time field from the look up condition.
so this suggests that OLEDB is set up correctly.

THe job also runs fine if i replace the OLEDB stage with ODBC.

THe generated code is given below.

* Tokens were replaced below as follows:
* Pin%%V0S1P1.Column%%1 <= DSLink2.DateField
* GET.Pin%%V0S1P1 <= GET.DSLink2
* Pin%%V0S1P1.REJECTEDCODE <= DSLink2.REJECTEDCODE
* Pin%%V0S1P3.Column%%1 <= DSLink6.EMP_ID
* Pin%%V0S1P3.Column%%2 <= DSLink6.DOB
* GET.Pin%%V0S1P3 <= GET.DSLink6
* Pin%%V0S1P3.REJECTEDCODE <= DSLink6.REJECTEDCODE
* Pin%%V0S1P2.Column%%1 <= DSLink4.EMP_ID
* PUT.Pin%%V0S1P2 <= PUT.DSLink4
* Pin%%V0S1P2.REJECTED <= DSLink4.REJECTED
* Pin%%V0S1P2.REJECTEDCODE <= DSLink4.REJECTEDCODE
*
* Subroutine for active stage OLEDBTEST.Transformer_1 generated at 15:30:43 04 AUG 2004
*
SUBROUTINE DSTransformerStage(HANDLES,ERROR)

$INCLUDE DSINCLUDE DSD_RTCONFIG.H
$INCLUDE DSINCLUDE DSD_STAGE.H
$INCLUDE DSINCLUDE DSD_BCI.H

$INCLUDE DSINCLUDE JOBCONTROL.H
DEFFUN DSRLoadString(Num,Text,Args) CALLING '*DataStage*DSR_LOADSTRING'
$DEFINE Pin%%V0S1P1.Column%%1 STAGECOM.ARR(1)
$DEFINE GET.Pin%%V0S1P1 CALL $DS.SEQGETNEXT(1,Pin%%V0S1P1.REJECTEDCODE)
IF STAGECOM.TRACE.STATS THEN CALL $PERF.NAME(-2,'DSLink6.Derivation')
$DEFINE Pin%%V0S1P3.Column%%1 STAGECOM.ARR(2)
$DEFINE Pin%%V0S1P3.Column%%2 STAGECOM.ARR(3)
$DEFINE GET.Pin%%V0S1P3 CALL $DSP.GetByKey(2,Pin%%V0S1P3.REJECTEDCODE)
IF STAGECOM.TRACE.STATS THEN CALL $PERF.NAME(-3,'DSLink4.Derivation')
$DEFINE Pin%%V0S1P2.Column%%1 STAGECOM.ARR(2)
$DEFINE PUT.Pin%%V0S1P2 CALL $DS.SEQPUT(3, Pin%%V0S1P2.REJECTEDCODE)

UPDATE.COUNT = STAGECOM.RATE



LOOP
REJECTED = @TRUE
* Get next row from primary input pin DSLink2
STAGECOM.PINNO = 1
GET.Pin%%V0S1P1
ERROR = Pin%%V0S1P1.REJECTEDCODE
WHILE NOT(ERROR)
* Primary Key derivation code for pin DSLink6
STAGECOM.PINNO = 2
IF STAGECOM.TRACE.STATS THEN CALL $PERF.BEGIN(-2)
Pin%%V0S1P3.Column%%2 = Pin%%V0S1P1.Column%%1
IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-2)
GET.Pin%%V0S1P3

STAGECOM.PINNO = 3
IF STAGECOM.TRACE.STATS THEN CALL $PERF.BEGIN(-3)
IF @TRUE THEN
* Column derivation code for pin DSLink4
Pin%%V0S1P2.REJECTED = @FALSE
IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-3)

PUT.Pin%%V0S1P2
IF NOT(Pin%%V0S1P2.REJECTEDCODE) THEN
REJECTED = @FALSE
END ELSE
Pin%%V0S1P2.REJECTED = @TRUE
END
END
ELSE
Pin%%V0S1P2.REJECTED = @TRUE
Pin%%V0S1P2.REJECTEDCODE = 0
END


UPDATE.COUNT -= 1
IF UPDATE.COUNT LE 0 THEN CALL DSD.Update(HANDLES);UPDATE.COUNT = STAGECOM.RATE
REPEAT
RETURN
END

Can anybody help me what the problem is ?

Thanks,

Dhiraj

[dhiraj]

oops i forgot to tell this.

When i import the table definition of a SQL server 2000 table Using an OLEDB plug in stage the metadata for date time field appears to be as time stamp of length 30 with scale as 0.
But when i do the same thing using an ODBC stage i get it as Timestamp of 23 with a scale of 3.

Is there any difference in the way OLE DB handles date time fields?

does this effect the problem i highlighted in the prvious post?

any help would be highly appreciated.

thanks

Dhiraj